/**
*	-------LotDao By Raj Vallamsetla    -------
*	------- Formated by Mallikarjun 06/25/2002 ----
*
*	This DAO is used for Lot Information
*/

package com.scalar.chiptrack.operations.process.dao;

import java.sql.*;

import java.util.Iterator;
import java.util.ArrayList;
import java.util.LinkedHashMap;

import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
//import com.scalar.chiptrack.db.*;

import com.scalar.ScalarException;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.SqlQueryMappings;

import com.scalar.chiptrack.operations.dataentry.forms.AssemblyDataEntryForm;
import com.scalar.chiptrack.operations.dataentry.forms.TestDataEntryForm;
import com.scalar.chiptrack.operations.dataentry.dao.BinQuantity;
import com.scalar.chiptrack.operations.lotcorrection.LotCorrection;
import com.scalar.chiptrack.operations.lotcorrection.forms.LotReworkForm;
import com.scalar.chiptrack.operations.process.Lot;
import com.scalar.chiptrack.operations.process.ParameterTypes;
import com.scalar.chiptrack.operations.process.ProcessTypes;
//import com.scalar.chiptrack.operations.dataentry.forms.AssemblyDataEntryForm;
import com.scalar.chiptrack.operations.dataentry.forms.InventoryDataEntryForm;
import com.scalar.chiptrack.operations.dataentry.TransitLotNumber;
import com.scalar.chiptrack.operations.boards.forms.BoardInventoryDataEntryForm;
import com.scalar.chiptrack.operations.LotReservation;
import com.scalar.chiptrack.adaptor.FabSortLot;
import com.scalar.chiptrack.adaptor.AssemblyLot;
import com.scalar.chiptrack.adaptor.TestLot;
import com.scalar.chiptrack.adaptor.SortLotBinData;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.commons.Constants;


public class LotDAO {


    public static void releaseReservedLots (DBTransaction dbTransaction, String lineItemNumberStr, String salesOrderIdStr) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;

        try {

            int lineItemNumber = Integer.parseInt (lineItemNumberStr);
            int salesOrderId = Integer.parseInt (salesOrderIdStr);

            if(dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.process.lot.releaseReservedLots"));
            cstmt.setInt (1, lineItemNumber);
            cstmt.setInt (2, salesOrderId);

            cstmt.execute();
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not release reserved Lots for lineItem# " + lineItemNumberStr + ", salesOrder ID: " + salesOrderIdStr, sqle);
        }

        finally {

            if (dbTransaction == null) {
            	try {
                    if (conn != null) {
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }

            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }



    // Added by Balaram on Dec 20, 2002
    public static void saveLotReservationList (DBTransaction dbTransaction, Iterator lotReservations) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;

        if (lotReservations == null) {
            return;
        }


        try {

            if(dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.lot.saveLotReservationList"));

            LotReservation lotReservation = null;

            while (lotReservations.hasNext()) {
                lotReservation = (LotReservation) lotReservations.next();
                //System.out.println("LotNumber " + lotReservation.getLotNumber() + " LineItem Qty = " + lotReservation.getLotQuantity());

                pstmt.setInt (1, lotReservation.getLineItemNumber());
                pstmt.setInt (2, lotReservation.getSalesOrderId());
                pstmt.setString (3, lotReservation.getLotNumber());
                pstmt.setInt (4, lotReservation.getLotQuantity());
                pstmt.setString (5, lotReservation.getExpectedDateOut());
                pstmt.setString (6, lotReservation.getReservedBy());
                pstmt.setString (7, lotReservation.getDateReserved());

                pstmt.addBatch();
            }

            pstmt.executeBatch();
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Lot Reservation Information into database", sqle);
        }

        finally {

            if (dbTransaction == null) {
            	try {
                    if (conn != null) {
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }


            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }



	/*public String getLotId(String lotNumber,String locationId,String processStepId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		String lotId = "";


		try{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.lotNumber"));
			pstmt.setString(1,lotNumber);
			pstmt.setString(2,locationId);
			pstmt.setString(3,processStepId);

			rs=pstmt.executeQuery();

			while(rs.next()){
			lotId =rs.getString(1);
			}
		}catch (Exception sqle) {
           	sqle.printStackTrace();
            throw new ScalarException ("Could not getLotId from database", sqle);
        }finally{
			if (pstmt != null) {
	        	try { pstmt.close(); } catch (SQLException ignore){}
	        }
	        if (conn != null) {
	                try { conn.close(); } catch (SQLException ignore){}
	        }
		}
		return lotId;


        }*/

	/*
	Bhaskar added this overloaded method inorder to avoid the javax.transaction.HeuristicRollBackException
	while getting back the Lot_id that is just now created during this transaction. The read identity is
	not being used here because it needs a number of methods to be changed. 21 Jan 2004
	*/

	public static String getLotId(String lotNumber,String locationId,String processStepId) throws ScalarException{
		return getLotId( null,  lotNumber,  locationId,  processStepId);
	}


	public static String getLotId(DBTransaction dbTransaction, String lotNumber,String locationId,String processStepId) throws ScalarException
	{

		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		String lotId = "";

		try
		{
			if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.lotNumber"));
			pstmt.setString(1,lotNumber);
			pstmt.setString(2,locationId);
			pstmt.setString(3,processStepId);

			rs=pstmt.executeQuery();

			while ( rs.next() )
			{
				lotId =rs.getString(1);
            }
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not getLotId from database", sqle);
		}
		finally
		{
            if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore){}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore){}
			}
		}

        return lotId;
	}


		public static String getPrevLotId(String lotNumber,int prevProcessStepId) throws ScalarException{


				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;
				 String lotId = "";


		try{


        conn = DataSource.getConnection();
		pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.prevProcessStepId"));
		pstmt.setString(1,lotNumber);
		pstmt.setInt(2,prevProcessStepId);
		rs=pstmt.executeQuery();

		while(rs.next()){
		lotId =rs.getString(1);
		}


	}
			catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Prev Process Step Id database", sqle);
        }


				 if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }

			return lotId;

        }




    public static String getLotIdByNumber(String lotNumber,String processStepId) throws ScalarException{

        Connection conn = null;
        ResultSet rs    = null;
        PreparedStatement pstmt=null;
        String lotId = "";

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.lotByNumber"));
            pstmt.setString(1,lotNumber);
            pstmt.setString(2,processStepId);
            rs=pstmt.executeQuery();
            while(rs.next())
            {
                lotId =rs.getString(1);
            }
	}
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get LotNumber ", sqle);
        }

        if (pstmt != null)
        {
            try {
                pstmt.close();
            }
            catch (SQLException ignore){}
        }
        if (conn != null)
        {
            try
            {
                conn.close();
            }
            catch (SQLException ignore){}
        }
        return lotId;
    }

    // Method Added by Raj Vallamsetla on 08/12/2002

	public static ArrayList getFabLotNumbers(String processStepId,String locationId) throws ScalarException{


				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;
				 ArrayList lotNumberList = new ArrayList();
				 Lot lot = null;




		try{


                conn = DataSource.getConnection();
		pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.lotNumbers"));
		pstmt.setString(1,processStepId);
		pstmt.setString(2,locationId);
		rs=pstmt.executeQuery();

		while(rs.next()){
		lot =new Lot(rs.getString(1),rs.getString(2));
		lotNumberList.add(lot);
		}


	}
			catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save get Lot Numbers from database", sqle);
        }


				 if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }

			return lotNumberList;

        }






	// Ends Here


	// Method Added by Raj Vallamsetla on 08/12/2002
	public static ArrayList getRunningLotNumbers(String processStepId,String locationId) throws ScalarException{


				 Connection conn = null;
				 ResultSet rs    = null;
				 CallableStatement cs=null;
				 ArrayList lotNumberList = new ArrayList();
				 Lot lot = null;




		try{


        conn = DataSource.getConnection();
		cs = conn.prepareCall(SqlQueryMappings.get ("operations.process.lot.getRunningLotNumbers"));
		cs.setString(1,processStepId);
		cs.setString(2,locationId);
		rs=cs.executeQuery();


		while(rs.next()){
		lot =new Lot(rs.getString(1),rs.getString(2));
		lot.setStatus(rs.getString(3));
		lotNumberList.add(lot);
		}


	}
			catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Ruuning and OnGoing Lot Numbers from database", sqle);
        }


				 if (cs != null) {
                try { cs.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }

			return lotNumberList;

        }


	// Ends Here

    // Added by Prabhanjan on 01 December 2003 for Data Entry.
    public static ArrayList getRunningLotNumbersByPSTypeName ( String processStepId, String locationId, String psTypeName ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        ArrayList lotNumberList = new ArrayList ();
        Lot lot = null;

        try
        {
            psTypeName = psTypeName + "%";

            conn = DataSource.getConnection ();
            pstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.dataentry.getRunningLotNumbersByPSType" ) );
            pstmt.setString ( 1, processStepId );
            pstmt.setString ( 2, locationId );
            pstmt.setString ( 3, psTypeName );

            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                lot = new Lot ( rs.getString ( 1 ), rs.getString ( 2 ) );
                lot.setStatus ( rs.getString ( 3 ) );
                lotNumberList.add ( lot );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not get Ruuning Lot Numbers from database", sqle );
        }

        finally
        {
            if ( rs != null )
            {
                try {     rs.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( pstmt != null )
            {
                try {     pstmt.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( conn != null )
            {
                try {    conn.close ();     }     catch ( SQLException ignore )      {      }
            }
        }
        return lotNumberList;
    }


    // Get all lots to Show for Boards/kits - for BRECIS - Added By Balaram - Mar 28, 2003
    public static ArrayList getAllLotNumbers (String processStepId,String locationId) throws ScalarException{

        Connection conn = null;
        ResultSet rs    = null;
        CallableStatement cs=null;
        ArrayList lotNumberList = new ArrayList();
        Lot lot = null;

        try {

            conn = DataSource.getConnection();

            cs = conn.prepareCall(SqlQueryMappings.get ("operations.process.lot.getAllLotNumbers"));

            cs.setString(1,processStepId);
            cs.setString(2,locationId);
            rs=cs.executeQuery();

            while(rs.next()) {
                lot =new Lot(rs.getString(1),rs.getString(2));
				lot.setStatus(rs.getString(3));
                lotNumberList.add(lot);
            }
		}
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save get Running and OnGoing Lot Numbers from database", sqle);
        }

        if (cs != null) {
            try { cs.close(); } catch (SQLException ignore){}
        }
        if (conn != null) {
            try { conn.close(); } catch (SQLException ignore){}
        }

        return lotNumberList;

    }




	public static String checkLotId(String lotId,String type,String stageId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		String checkLotId = "";


		try{
			conn = DataSource.getConnection();
			if(type.equals("stage"))
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.stageLotId"));
			else
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.subStageLotId"));

			pstmt.setString(1,lotId);
			pstmt.setString(2,stageId);

			rs=pstmt.executeQuery();

			while(rs.next()){
			checkLotId ="exists";
			}
		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not check lotId from database", sqle);
        }finally{
			if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return checkLotId;

	}

	public static String getSortLotId(String lotId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		String checkLotId = "";


		try{
	        conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.sortlotId"));
			pstmt.setString(1,lotId);

			rs=pstmt.executeQuery();

			while(rs.next()){
			checkLotId ="exists";
			}
		}catch (Exception sqle) {
            sqle.printStackTrace();
           throw new ScalarException ("Could not save FabProcess SetUp from database", sqle);
        }finally{
		 	if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}

		return checkLotId;

	}

	public static String getAssemblyLotId(String lotId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		String checkLotId = "";


		try{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.assemblylotId"));
			pstmt.setString(1,lotId);

			rs=pstmt.executeQuery();

			while(rs.next()){
			checkLotId ="exists";
			}
		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save FabProcess SetUp from database", sqle);
        }finally{
	 		if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return checkLotId;

	}

    public static String getInventoryLotId(String lotId) throws ScalarException{
        Connection conn = null;
        ResultSet rs    = null;
        PreparedStatement pstmt=null;
        String checkLotId = "";

        try{
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.inventorylotId"));
            pstmt.setString(1,lotId);

            rs=pstmt.executeQuery();

            while(rs.next()){
                checkLotId ="exists";
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save FabProcess SetUp from database", sqle);
        }
        finally
        {
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore){}
            }
        }
        return checkLotId;

    }

	public static String getTestLotId(String lotId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		String checkLotId = "";


		try{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.testlotId"));
			pstmt.setString(1,lotId);

			rs=pstmt.executeQuery();

			while(rs.next()){
			checkLotId ="exists";
			}
		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Test lot Id from database", sqle);
        }finally{
	 		if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return checkLotId;

	}


	public static Lot getFabLotData(String lotId,String type,String stageId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		Lot lot = new Lot();


		try{
			conn = DataSource.getConnection();

			//System.out.println("lot id "+lotId);
			//System.out.println("type "+type);
			//System.out.println("stageId "+stageId);


			if(type.equals("stage"))
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getFabStageLot"));
			else
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getFabSubStageLot"));

			pstmt.setString(1,lotId);
			pstmt.setString(2,stageId);

			rs=pstmt.executeQuery();

			while(rs.next()){
			lot.setQuantityIn(rs.getString("FSL_Qty_In"));
			lot.setQuantityOut(rs.getString("FSL_Qty_Out"));
			lot.setDateIn(rs.getString("FSL_Date_In"));
			lot.setDateOut(rs.getString("FSL_Date_Out"));
			lot.setYield(rs.getString("FSL_Yield"));

			}
		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get fab lot data from database", sqle);
        }finally{
	 		if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return lot;

	}

    // Added by Prabhanjan on 01 December 2003
    public static Lot getFabLotData ( String lotId ) throws ScalarException
    {
         Connection conn = null;
         ResultSet rs = null;
         PreparedStatement pstmt = null;
         Lot lot = new Lot ();
         try
         {
            conn = DataSource.getConnection ();

            //pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.lot.getFabLot" ) );
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.lot.getFabLotAndDateCode" ) );
            pstmt.setString ( 1, lotId );

            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                lot.setLotNumber    ( rs.getString ( "Lot_Number" ) );
                lot.setStatus       ( rs.getString ( "Lot_Current_Status" ) );
                lot.setQuantityIn   ( rs.getString ( "FL_Input_Quantity" ) );
                lot.setQuantityOut  ( rs.getString ( "FL_Output_Quantity" ) );
                lot.setDateIn       ( rs.getString ( "FL_Date_In" ) );
                lot.setDateOut      ( rs.getString ( "FL_Date_Out" ) );
                lot.setYield        ( rs.getString ( "FL_Yield" ) );
                lot.setPOrderID		( rs.getString ( "PurchaseOrder_ID" ) );
                lot.setDieSize		( rs.getString ( "FL_Die_Size" ) );

				String grossDie = "";
				try
				{
					grossDie = rs.getString ( "FL_Gross_Die_Wafer" );
					if ( ( grossDie == null ) || ( grossDie.trim().length() == 0 ) )
					{
						grossDie = "";
					}
				}
				catch(Exception e)
				{
					grossDie = "";
				}
				lot.setGrossDie		( grossDie );
                lot.setDateCode		( rs.getString ( "Lot_Date_Code" ) );
                lot.setVendorLot	( rs.getString ( "FL_Vendor_Lot_Number" ) );
                lot.setComments 	( rs.getString ( "Lot_Comments" ) );
                lot.setHold ( ( rs.getBoolean( "Is_Hold" ) ) ? false : true );
                lot.setWaferId ( rs.getString ( "Wafer_Id" ) );
            }

         }
         catch ( Exception sqle )
         {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not get fab lot data from database", sqle );
         }
         finally
         {
             if (rs != null)
             {
              try { rs.close(); } catch (SQLException ignore){}
             }
             if (pstmt != null)
             {
                try { pstmt.close(); } catch (SQLException ignore){}
             }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
         }
         return lot;
    }



    public static Lot getSortLotData(String lotId) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs    = null;
        PreparedStatement pstmt=null;
        Lot lot = new Lot();

        try
        {
            conn = DataSource.getConnection();
            //pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getSortLot"));
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getSortLotAndDataCode"));

            pstmt.setString(1,lotId);

            rs=pstmt.executeQuery();

            /*while(rs.next())
            {
                //Added by Rohan on July 28th 2004
                lot.setLotNumber( rs.getString( "Lot_Number" ) );
                lot.setStatus   ( rs.getString( "Lot_Current_Status" ) );
                //End of additions by Rohan on July 28th 2004

                lot.setQuantityIn(rs.getString("SL_Wafer_Qty"));
                lot.setQuantityOut(rs.getString("SL_Qty_Out"));
                lot.setDateIn(rs.getString("SL_Date_In"));
                lot.setDateOut(rs.getString("SL_Date_Out"));
                lot.setDieWafer(rs.getString("SL_Die_Wafer"));
                lot.setRma(rs.getString("SL_RMA_Critiria"));
                lot.setYield(rs.getString("SL_Yield"));
                lot.setPOrderID( rs.getString("PurchaseOrder_ID") );
                String avgGoodDie = rs.getString ("sl_avg_good_die");
                if ( avgGoodDie.trim().length() > 0 )
                {
                    lot.setAvgGoodDie( StringUtils.formatAmount(new Double(avgGoodDie).doubleValue(), "###.##"));
                }
                else
                {
                    lot.setAvgGoodDie("");
                }
                lot.setDieSize ( rs.getString ( "SL_Die_Size" ) );
                lot.setDateCode ( rs.getString ( "Lot_Date_Code" ) );
                lot.setVendorLot ( rs.getString ( "SL_Vendor_Lot_Number" ) );
                lot.setComments ( rs.getString ( "Lot_Comments" ) );
                lot.setHold ( ( rs.getBoolean( "Is_Hold" ) ) ? false : true );
            }*/
            while(rs.next())
            {
                lot.setLotNumber( rs.getString( "Lot_Number" ) );
                lot.setStatus   ( rs.getString( "Lot_Current_Status" ) );
                lot.setQuantityIn(rs.getString("Wafer_Qty_In"));
                lot.setQuantityOut(rs.getString("Wafer_Qty_Out"));
                lot.setDateIn(rs.getString("Date_In"));
                lot.setDateOut(rs.getString("Date_Out"));
                lot.setYield(rs.getString("SL_Yield") );
                lot.setGrossDie( rs.getString("SL_Gross_Die") );
                lot.setPOrderID( rs.getString( "Po_Number") );
                lot.setDateCode ( rs.getString ( "Lot_Date_Code" ) );
                lot.setComments ( rs.getString ( "Lot_Comments" ) );
                lot.setLotCategory ( rs.getString ( "Lot_Category" ) );
                lot.setVendorLot ( rs.getString ( "vendor_lot_number" ) );

            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get sort lot data from database", sqle);
        }
        finally
        {
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return lot;
    }

    // added by hareesh on 6th Decemeber 2003

    public static ArrayList getTotalNoOfBins(String processStepId) throws ScalarException{
         Connection conn = null;
         ResultSet rs    = null;
         PreparedStatement pstmt=null;
         int goodBins = 0;
         int rejectedBins = 0;
         try{
             conn = DataSource.getConnection();
            //System.out.println("Processstep"+processStepId);
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.dataentry.getTotalNoOfBinsGoodAndRejected"));
            pstmt.setString(1,processStepId);
            rs = pstmt.executeQuery();
            while ( rs.next() ){
                String accept = rs.getString("WSBT_Bin_Type");
                if(accept != null){
                    if ( ("Accept").equalsIgnoreCase(accept)){
                        goodBins++;
                    }else{
                        rejectedBins++;
                    }
                }else{
                     rejectedBins++;
                }
            }
            //System.out.println("Goodbins" +goodBins);
            //System.out.println("rejectedBins" +rejectedBins );
            // comment this if not required. --- check this onces..----------------
            if(goodBins == 0 ){
                  goodBins = 1; // assumed by seening adaptor umc_sort_bin_summary_adaptor
				  /*The above value is now set to 1 after an assumption that we will place a good bin count of 1 if
				  good bins count turns out to be 0. Bhaskar on 04 Feb 2004*/
            }
            if ( goodBins > 15){
                goodBins = 15; // table does not support more that this
            }
            if(rejectedBins == 0 ) {
               rejectedBins = 1;
            }

            rs.close();
            rs = null;
            pstmt.close();
            pstmt = null;
         }catch (Exception sqle) {
             sqle.printStackTrace();
             throw new ScalarException ("Could not get sort Bins count from the database", sqle);
         }finally{
              if (pstmt != null) {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null) {
                 try { conn.close(); } catch (SQLException ignore){}
             }
         }
         ArrayList returnList = new ArrayList(2);
         returnList.add(0,goodBins+"" );
         returnList.add(1,rejectedBins+"" );
         return returnList;

     }

    public static SortLotBinData getSortLotBinData (String lotId ) throws ScalarException {
        Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		SortLotBinData sortLotBinData = null;

		try{
			conn = DataSource.getConnection();


            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getSortLotHistoryData"));
            pstmt.setString(1,lotId);
			rs=pstmt.executeQuery();
            String sL_Die_Wafer="";
            String sL_Yield ="";
            if(rs.next()){
                  sL_Die_Wafer = rs.getString ( "SL_Die_Wafer" );
                  sL_Yield = rs.getString ( "SL_Yield" );
            }
            rs.close();
            rs = null;
            pstmt.close();
            pstmt = null;

			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getSortLotBinData"));
			pstmt.setString(1,lotId);
			rs=pstmt.executeQuery();
            //select Lot_ID, Wafer_ID, Test_Time, Good_Dice, Yield, Bin_1, Bin_2, Bin_3, Bin_4, Bin_5, Bin_6,
            //Bin_7, Bin_8, Bin_9, Bin_10, Bin_11 from Informative_Binning from Lot_ID = ?

            ArrayList waferList = new ArrayList();
            ArrayList totalList = new ArrayList();
            ArrayList averageList = new ArrayList();
            ArrayList precentageList = new ArrayList();
            ArrayList binList = new ArrayList();
            boolean binDataFound = false;
			while(rs.next()){
                binDataFound = true;
                 ArrayList temp_it = new ArrayList(6);
                temp_it.add ( 0, rs.getString ( "Wafer_ID" ) );
                temp_it.add ( 1, rs.getString ( "Test_Time" ) ); // test time
                temp_it.add ( 2, rs.getString ( "Good_Dice" ) ); // assumed good Deice = Bin1
                temp_it.add ( 3, rs.getString ( "Yield") ); // yeild
                String tempBin1 = rs.getString ( "Bin_1" );
                temp_it.add ( 4, tempBin1 ); // bin1
                temp_it.add ( 5, rs.getString ( "Bin_Rejected") );
                waferList.add ( temp_it );
                //System.out.println(temp_it);
                ArrayList temp_binList = new ArrayList(15);
                temp_binList.add( 0 , tempBin1 );
                //System.out.println("tempBin1"+tempBin1);
                for(int bincount =0; bincount < 14; bincount++  ){  /////////////
                    temp_binList.add( bincount+1 , rs.getString ( "Bin_"+(bincount+2) ) ); // bin2 to bin15
                    //System.out.println(bincount+1+"..."+temp_binList.get( bincount+1 ));
                }
                binList.add(temp_binList);
                //System.out.println(temp_binList);
			}
            if(binDataFound){
                /*sortLot.getVendorName(),  // vendorName
                sortLot.getCustProduct(), // product
                sortLot.getLotId(),       // LotId
                null,                     // program
                null,                     // tester
                null,                     // probecard
                null,                     // grossDice
                null,                     // shipSlice
                null,                     // shipDice
                null,                     // shipYield
                null,                     // shipTime
                null,                     // reportTime
                waferList,                //waferIdList
                new ArrayList(),           //totalList // dumy
                new ArrayList(),           //averageList // dumy
                new ArrayList(),           //precentageList // dumy
                binList                    //binList*/
            sortLotBinData = new SortLotBinData(null, null,lotId,null,null,null,
                                                sL_Die_Wafer,null, null,sL_Yield,null, null,
                                                waferList,totalList, averageList, precentageList, binList );
            }

		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Sort Bin lot data from database", sqle);
        }finally{
	 		if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return sortLotBinData;
    }

	public static AssemblyDataEntryForm getAssemblyLotData(String lotId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		AssemblyDataEntryForm assydata = new AssemblyDataEntryForm();


		try{
			conn = DataSource.getConnection();
            // Modified on 22nd July 2004 by KumaraSwamy to get the Date Code
			//pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getAssemblyLot"));
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getAssemblyLotAndDateCode"));
			pstmt.setString(1,lotId);

			rs=pstmt.executeQuery();
            String poNumber = null;
			while(rs.next()){

            //Added by Rohan on July 28th 2004     ,
            assydata.setLotNo( rs.getString( "Lot_Number" ) );
            assydata.setLotStatus( rs.getString( "Lot_Current_Status" ) );
            //End of additions by Rohan on July 28th 2004
			assydata.setLc(rs.getString("AL_LC"));
			assydata.setPackageType(rs.getString("AL_Package_Type"));
            poNumber = rs.getString("PurchaseOrder_ID") ;
			assydata.setPackageSize(rs.getString("AL_Package_Size"));
			assydata.setDiceDiscrepancyIn(rs.getString("AL_Dice_Discrp_In"));
			assydata.setDiceDiscrepancyOut(rs.getString("AL_Dice_Discrp_Out"));
			assydata.setDiceDiscrepancyYield( formatYieldToTwoDecimals (rs.getString("AL_Dice_Discrp_Yield") ) );
			assydata.setDiceInspectIn(rs.getString("AL_Die_Inspect_In"));
			assydata.setDiceInspectOut(rs.getString("AL_Die_Inspect_Out"));
			assydata.setDiceInspectYield( formatYieldToTwoDecimals ( rs.getString ("AL_Die_Inspect_Yield") ) );
			assydata.setPreMouldIn(rs.getString("AL_Pre_Mold_In"));
			assydata.setPreMouldOut(rs.getString("AL_Pre_Mold_Out"));
			assydata.setPreMouldYield( formatYieldToTwoDecimals ( rs.getString("AL_Pre_Mold_Yield") ) );
			assydata.setIcFinishedIn(rs.getString("AL_Ic_Finishing_In"));
			assydata.setIcFinishedOut(rs.getString("AL_Ic_Finishing_Out"));
			assydata.setIcFinishedYield( formatYieldToTwoDecimals ( rs.getString("AL_Ic_Finishing_Yield") ) );
			assydata.setInvYield( formatYieldToTwoDecimals ( rs.getString("AL_Inv_Yield") ) );
			assydata.setActYield( formatYieldToTwoDecimals ( rs.getString("AL_Act_Yield") ) );
			assydata.setAsyYield( formatYieldToTwoDecimals ( rs.getString("AL_Asy_Yield") ) );
			assydata.setQuantityIn(rs.getString("AL_Qty_In"));
			assydata.setQuantityOut(rs.getString("AL_Qty_Out"));
			assydata.setDateIn(rs.getString("AL_Date_In"));
			assydata.setDateOut(rs.getString("AL_Date_Out"));
			assydata.setVendorLotNo(rs.getString("AL_Vendor_Lot_Number"));//Bhaskar added this line on 02 Feb 2004
            assydata.setDateCode(rs.getString("Lot_Date_Code"));//Added by KumaraSwamy on 22nd July, 2004.
            assydata.setComments(rs.getString("Lot_Comments")); //Added by KumaraSwamy on 3rd August, 2004.
            assydata.setHold ( ( rs.getBoolean( "Is_Hold" ) ) ? false : true );  //Added by KumaraSwamy on 18th Oct, 2004.
            // changed by hareesh on 21st February 2004
            // -- START --
            // Instead storing in corresponding poNumber in
            assydata.setPoNumber( poNumber );
            /*if ( ( poNumber != null ) && ( poNumber.trim().length() != 0  ) ){
                int poNo = -1;
                try{
                    poNo = Integer.parseInt(poNumber);
                }catch(Exception ex){
                    poNo = -1;
                }
                if(poNo != -1){
                    assydata.setPoNumber( getPoNumber(poNo) );
                }
            } */
            //  -- END --
    	    }

		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get assembly lot data from database", sqle);
        }finally{
	 		if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return assydata;

	}




public static TestDataEntryForm getTestLotData(String lotId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		TestDataEntryForm testLot = new TestDataEntryForm();


		try{
			conn = DataSource.getConnection();
            // Modified By KumaraSwamy for getting the DateCode.
			//pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getTestLot"));
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getTestLotAndDateCode"));
			pstmt.setString(1,lotId);
			rs=pstmt.executeQuery();
			//System.out.println("In getTestLotData "+lotId);
            String poNumber = null;
			if(rs.next()){
            poNumber =  rs.getString("PurchaseOrder_ID") ;
            //Added by Rohan on July 28th 2004            ,
            testLot.setLotNo( rs.getString( "Lot_Number" ) );
            testLot.setStatus( rs.getString( "Lot_Current_Status" ) );
            //End of additions by Rohan on July 28th 2004
			testLot.setQuantityIn(rs.getString("FTL_Qty_In"));
			testLot.setQuantityOut(rs.getString("FTL_Qty_Out"));
			testLot.setPackageType(rs.getString("FTL_Package_Type"));
			testLot.setScheduleNo(rs.getString("FTL_Schedule_Number"));
			testLot.setDateIn(rs.getString("FTL_Date_In"));
			testLot.setDateOut(rs.getString("FTL_Date_Out"));
			testLot.setQuantityReject(rs.getString("FTL_Qty_Reject"));
			//Bhaskar added this code block for yield on 06 Feb 2004
			String yield = "";
			try
			{
				yield = rs.getString("FTL_Yield");
				if ( ( ( yield != null ) && ( yield.equalsIgnoreCase("null") ) ) || ( yield == null ) || ( yield.trim().length()==0 ) )
				{
					yield = "";
				}
				else
				{
					yield = StringUtils.formatAmount((float)(new Double(yield).doubleValue()), "###.##");
				}
			}
			catch ( Exception e )
			{
				yield = "";
			}

			testLot.setYield ( yield );
            // Added by Prabhanjan on 04 December 2003
            testLot.setLc(rs.getString("FTL_Lead_Count"));
            testLot.setRecipeId(rs.getString("FTL_Equipment_ID"));
            testLot.setTestProgramRevision(rs.getString("FTL_Test_Program_Revision"));
            testLot.setDateCode(rs.getString("Lot_Date_Code"));
            testLot.setVendorLot(rs.getString("FTL_Vendor_Lot_Number"));
            testLot.setComments(rs.getString("Lot_Comments"));
            testLot.setPoNumber( poNumber );
            testLot.setHold( ( rs.getBoolean( "Is_Hold" ) ) ? false : true );
            /*if ( ( poNumber != null ) && ( poNumber.trim().length() != 0  ) ){
                int poNo = -1;
                try{
                    poNo = Integer.parseInt(poNumber);
                }catch(Exception ex){
                    poNo = -1;
                }
                if(poNo != -1){
                    testLot.setPoNumber( getPoNumber(poNo) );
                }
            } */

            //System.out.println("DAO Qty In "+testLot.getQuantityIn());

			}
		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get sort lot data from database", sqle);
        }finally{
	 		if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return testLot;

	}




public static InventoryDataEntryForm getInventoryLotData(String lotId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		InventoryDataEntryForm inventoryLot = new InventoryDataEntryForm();


		try{
			conn = DataSource.getConnection();
            //Modified by KumaraSwamy on 22nd July, 2004.
			//pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getInventoryLot"));
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getInventoryLotAndDateCode"));
			pstmt.setString(1,lotId);
			rs=pstmt.executeQuery();
			//System.out.println("In getInventoryLotData "+lotId);

			if(rs.next()){
			inventoryLot.setQuantityIn(rs.getString("IL_Qty_In"));
			inventoryLot.setQuantityOut(rs.getString("IL_Qty_Out"));
			inventoryLot.setDateIn(rs.getString("IL_Date_In"));
			inventoryLot.setDateOut(rs.getString("IL_Date_Out"));
            inventoryLot.setDateCode(rs.getString("Lot_Date_Code"));
            //Added by Rohan on July 28th 2004
            inventoryLot.setLotNo(rs.getString("Lot_Number"));
            inventoryLot.setLotStatus(rs.getString("Lot_Current_Status"));
            //End of additions by Rohan on July 28th 2004
            //Added by KumaraSwamy on August 4th, 2004
            inventoryLot.setVendorLot(rs.getString("IL_Vendor_Lot_Number"));
            inventoryLot.setComments(rs.getString("Lot_Comments"));
            inventoryLot.setHold( ( rs.getBoolean( "Is_Hold" ) ) ? false : true );
            //End of addition by KumaraSwamy on August 4th, 2004.
			//inventoryLot.set

            inventoryLot.setWaferQtyIn( rs.getString ( "IL_Wafer_Qty_In" ) );
            inventoryLot.setWaferId( rs.getString ( "Wafer_id" ) );

			}
		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Inventory lot data from database", sqle);
        }finally{
	 		if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return inventoryLot;

	}


    public static BoardInventoryDataEntryForm getBoardInventoryLotData ( String lotId ) throws ScalarException{

        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        BoardInventoryDataEntryForm inventoryLot = new BoardInventoryDataEntryForm();

        try{
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.lot.getBoardInventoryLot"));
            pstmt.setString (1,lotId);
            rs=pstmt.executeQuery();

            //System.out.println ("In getBoardInventoryLotData " + lotId);

            if (rs.next()) {
                inventoryLot.setQuantityIn (rs.getString("IL_Qty_In"));
                inventoryLot.setQuantityOut (rs.getString("IL_Qty_Out"));
                inventoryLot.setDateIn (rs.getString("IL_Date_In"));
                inventoryLot.setDateOut (rs.getString("IL_Date_Out"));
                inventoryLot.setTestQuantity ( rs.getString ("IL_Test_Qty") );
                inventoryLot.setRejectedQty( rs.getString ("IL_Rejected_Qty"));
                inventoryLot.setDateCode( rs.getString ("Lot_Date_Code"));
            }
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Board Inventory lot data from database", sqle);
        }

        finally{
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return inventoryLot;
    }





	public static void saveFabLot(DBTransaction dbTransaction,String lotId,String type,String stageId,String quantityIn,String quantityOut,String yield,String dateIn,String dateOut) throws ScalarException{
 		Connection conn = null;
		PreparedStatement pstmt=null;
		String checkLotId ="";

		try{
			  if(dbTransaction == null) {
            	conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

			  checkLotId=checkLotId(lotId,type,stageId);

			  if(checkLotId.equals("exists")){
					if(type.equals("stage")){
						  pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.fabLotUpdateStage"));
						  pstmt.setString(1,stageId);
						  pstmt.setString(2,quantityIn);
						  pstmt.setString(3,quantityOut);
						  pstmt.setString(4,yield);
						  pstmt.setString(5,dateIn);
						  pstmt.setString(6,dateOut);
						  pstmt.setString(7,lotId);
						  pstmt.setString(8,stageId);
						  pstmt.executeUpdate();

				  	}

				  	if(type.equals("substage")){
						  //System.out.println("In Insert");
						  pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.fabLotUpdateSubStage"));
						  pstmt.setString(1,stageId);
						  pstmt.setString(2,quantityIn);
						  pstmt.setString(3,quantityOut);
						  pstmt.setString(4,yield);
						  pstmt.setString(5,dateIn);
						  pstmt.setString(6,dateOut);
						  pstmt.setString(7,lotId);
						  pstmt.setString(8,stageId);
						  pstmt.executeUpdate();

				  	}

			  }else{
				  	if(type.equals("stage")){
						  pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.fabLotInsertStage"));
						  pstmt.setString(1,lotId);
						  pstmt.setString(2,stageId);
						  pstmt.setString(3,quantityIn);
						  pstmt.setString(4,quantityOut);
						  pstmt.setString(5,yield);
						  pstmt.setString(6,dateIn);
						  pstmt.setString(7,dateOut);
						  pstmt.executeUpdate();

				  	}

				  	if(type.equals("substage")){
						  pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.fabLotInsertSubStage"));
						  pstmt.setString(1,lotId);
						  pstmt.setString(2,stageId);
						  pstmt.setString(3,quantityIn);
						  pstmt.setString(4,quantityOut);
						  pstmt.setString(5,yield);
						  pstmt.setString(6,dateIn);
						  pstmt.setString(7,dateOut);
						  pstmt.executeUpdate();

				  	}
			  }

		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save FabProcess SetUp from database", sqle);
        }finally{

			if (dbTransaction == null) {
            	try {
                	if (conn != null) {
						//System.out.println("just before commit");
                        conn.commit();
                    }

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

    		if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
	}

    // Added by Prabhanjan on 01 December 2003 for FAB Data Entry
    public static void saveFabLot ( DBTransaction dbTransaction, ArrayList wipSummaryList, String userName ) throws ScalarException
    {
		Connection conn = null;
        PreparedStatement pstmt1 = null;
        boolean success = false;

        try
        {
			if ( dbTransaction == null )
            {
            	conn = DataSource.getConnection ();
			}
            else
            {
            	conn = dbTransaction.getConnection ();
			}

			pstmt1 = conn.prepareStatement ( SqlQueryMappings.get ( "operations.dataentry.createUpdateFabLot" ) );

            FabSortLot wipSummary = null;
            int size = wipSummaryList.size ();
            String partNumber = null;
            String lotNumber = null;

            for ( int i = 0; i < size; i++ )
            {
                wipSummary = ( FabSortLot ) wipSummaryList.get ( i );

                partNumber = wipSummary.getCustProduct ();

                lotNumber = wipSummary.getLotId ();
                if ( lotNumber == null ) continue;

                pstmt1.setString ( 1, partNumber );
                pstmt1.setString ( 2, wipSummary.getLotId () );
                pstmt1.setString ( 3, wipSummary.getLotStatus () );
                pstmt1.setString ( 4, wipSummary.getQtyIn () );
                pstmt1.setString ( 5, wipSummary.getPoNumber () );
                pstmt1.setString ( 6, wipSummary.getDateIn () );
                pstmt1.setString ( 7, wipSummary.getStage () );
                pstmt1.setString ( 8, wipSummary.getStagePosition () );
                pstmt1.setString ( 9, wipSummary.getForcastedDateOut () );
                pstmt1.setString ( 10, userName );
                pstmt1.setString ( 11, wipSummary.getVendorName () );
                pstmt1.setString ( 12, wipSummary.getQtyOut () );
                pstmt1.setString ( 13, wipSummary.getDateOut () );
				pstmt1.setString ( 14, wipSummary.getGrossDie() );
                pstmt1.setString ( 15, wipSummary.getDateCode() );
                pstmt1.setString ( 16, wipSummary.getVendorLot() );
                pstmt1.setString ( 17, wipSummary.getComments() );
                /*pstmt1.setString ( 19, wipSummary.getPartialshipQtyIn());
				pstmt1.setString ( 20, wipSummary.getPartialshipQtyOut());
				pstmt1.setString ( 21, wipSummary.getPartialShipDateOut() );*/
                pstmt1.setString ( 18, wipSummary.getWaferID() );
                pstmt1.addBatch ();
            }
            pstmt1.executeBatch ();
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not save Fab Dataentry details into database", sqle );
        }
        finally
        {

            if (dbTransaction == null)
            {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt1 != null) {
                try { pstmt1.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    /* 	 Method Added By RAJ VALLAMSETLA for updating the fab lot with the Quantity Out,Dated Out

	   */
	public static void updateFabLot(DBTransaction dbTransaction,String lotId) throws ScalarException{
		Connection conn = null;
		PreparedStatement pstmt=null;
		FabLot fabLot =null;

		try{

		 	if(dbTransaction == null) {
            	conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}


		  	fabLot=getFabLot(Integer.parseInt(lotId));
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.updateFabLot"));
			pstmt.setString(1,fabLot.getQuantityOut());
			pstmt.setString(2,fabLot.getDateOut());
			pstmt.setString(3,fabLot.getYield());
			pstmt.setString(4,lotId);
			pstmt.executeUpdate();


		 }catch (Exception sqle) {
            sqle.printStackTrace();
			System.out.println("Sql exception ");
           throw new ScalarException ("Could not save FabProcess SetUp from database", sqle);
        }finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
            	try {
                	if (conn != null) {
						//System.out.println("just before commit");
                        conn.commit();
                    }

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
	}


	public static void updateFabLotDateOut(DBTransaction dbTransaction,String lotId,int processStepId,int locationId) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		ArrayList fabStageSubStageList =(ArrayList) FabDAO.getFabStages(processStepId,locationId );
		int lastIndex = fabStageSubStageList.size()-1;
		FabStage fabStage = (FabStage) fabStageSubStageList.get(lastIndex);
		String type=fabStage.getType();
		int stageId = fabStage.getStageId();
		String qtyOut  = null;
		String dateOut = null;
		String yield   = null;

		try{

		 	if(dbTransaction == null) {
            	conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}
			if(type.equals("stage"))
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getFabStageLotDateOut"));

			if(type.equals("substage"))
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.getFabSubStageLotDateOut"));

			pstmt.setString(1,lotId);
			pstmt.setInt(2,stageId);
			rs=pstmt.executeQuery();
			while(rs.next()){
			qtyOut=rs.getString("FSL_Qty_Out");
			dateOut=rs.getString("FSL_Date_Out");
			yield=rs.getString("FSL_Yield");
			}
			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.UpdateFabLotDateOut"));
			pstmt.setString(1,qtyOut);
			pstmt.setString(2,dateOut);
			pstmt.setString(3,yield);
			pstmt.setString(4,lotId);
			pstmt.executeUpdate();
		 }catch (Exception sqle) {
            sqle.printStackTrace();
			System.out.println("Sql exception ");
           throw new ScalarException ("Could not UpDate FabLot Date Out and Qty out to database", sqle);
        }finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
            	try {
                	if (conn != null) {
						//System.out.println("just before commit");
                        conn.commit();
                    }

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
	}




   /*	Method Added by	  RAJ VALLAMSETLA to get the fab details

	 */
   public static FabLot getFabLot(int lotId) throws ScalarException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		FabLot fabLot = null;

		try{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.fabStageLot"));
			pstmt.setInt(1, lotId);
            rs = pstmt.executeQuery();

			//System.out.println("lotId in getFabLot "+lotId);

			while(rs.next()) {
				fabLot=new FabLot(rs.getString(1),rs.getString(2),rs.getString(3));
			}

		}catch (Exception sqlex) {

			sqlex.printStackTrace();
			throw new ScalarException ("Could not get fabLot  from database", sqlex);

		}finally {

			if (rs != null) {
				try { rs.close(); } catch (SQLException excp){}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException excp){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException excp){}
			}
		}

		if(fabLot==null)
		fabLot = new FabLot();

		return fabLot;
	}



	public static void saveSortLot(DBTransaction dbTransaction,String lotId,String waferQuantity,String dieWafer,String dateIn,String dateOut,String quantityOut,String rma) throws ScalarException  {
		Connection conn = null;
		PreparedStatement pstmt=null;
		String checkLotId ="";

		try{
			  if(dbTransaction == null) {
            	conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

			  checkLotId=getSortLotId(lotId);

			  if(checkLotId.equals("exists")){

				  //System.out.println("In Exists");
				  pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.sortLotUpdate"));
				  pstmt.setString(1,waferQuantity);
				  pstmt.setString(2,dieWafer);
				  pstmt.setString(3,dateIn);
				  pstmt.setString(4,dateOut);
				  pstmt.setString(5,quantityOut);
				  pstmt.setString(6,rma);
				  pstmt.setString(7,lotId);
				  pstmt.executeUpdate();

			  }else{
				  //System.out.println("In Insert");
				  pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.sortLotInsert"));
				  pstmt.setString(1,lotId);
				  pstmt.setString(2,waferQuantity);
				  pstmt.setString(3,dieWafer);
				  pstmt.setString(4,dateIn);
				  pstmt.setString(5,dateOut);
				  pstmt.setString(6,quantityOut);
				  pstmt.setString(7,rma);
				  pstmt.executeUpdate();

			  }

		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save FabProcess SetUp from database", sqle);
        }finally{


			 if (dbTransaction == null) {
            	try {
                	if (conn != null) {
						//System.out.println("just before commit");
                        conn.commit();
                    }

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }


		  	if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
	}
	// Added by Prabhanjan on 02 December 2003
	public static void saveSortLot ( DBTransaction dbTransaction, ArrayList wipSummaryList, String userName, String sortType ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection ();
			}
			else
			{
				conn = dbTransaction.getConnection ();
			}

			pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.createUpdateSortLotDataEntry"));

			FabSortLot wipSummary = null;
			int size = wipSummaryList.size();

			String partNumber = null;
			String lotNumber = null;

                for (int i=0; i<size; i++) {
                    wipSummary = (FabSortLot) wipSummaryList.get (i);

                    partNumber = wipSummary.getCustProduct();

                    lotNumber = wipSummary.getLotId();
                    if (lotNumber == null) continue;

                    pstmt.setString (1, partNumber);
                    pstmt.setString (2, wipSummary.getLotId());
                    pstmt.setString (3, wipSummary.getLotStatus());
                    pstmt.setString (4, wipSummary.getQtyIn());
                    pstmt.setString (5, wipSummary.getPoNumber());
                    pstmt.setString (6, wipSummary.getDateIn());
                    pstmt.setString (7, wipSummary.getStage());
                    pstmt.setString (8, wipSummary.getForcastedDateOut());
                    pstmt.setString (9, wipSummary.getVendorName());
                    pstmt.setString (10, wipSummary.getQtyOut());
                    pstmt.setString (11, wipSummary.getDateOut());
                    pstmt.setString (12, sortType);
                    pstmt.setString (13, wipSummary.getGrossDie() );
                    pstmt.setString (14, wipSummary.getShipYield() );
					pstmt.setString (15, null );
					pstmt.setString (16, wipSummary.getAvgGoodDie() );
                    pstmt.setString (17, wipSummary.getDateCode() );
                    pstmt.setString (18, wipSummary.getVendorLot() );
                    pstmt.setString (19, wipSummary.getComments() );
                    pstmt.setString(20, wipSummary.getLotCategory() );
                    pstmt.addBatch();
                }

                pstmt.executeBatch();

                success = true;

            }
            catch ( Exception sqle )
            {
                sqle.printStackTrace ();
                throw new ScalarException ( "Could not save Sort Process SetUp from database", sqle );
            }
            finally
            {

                if (dbTransaction == null)
                {
                    try {
                        if (conn != null) {
                            if (success == true) {
                                conn.commit();
                            }
                            else {
                                conn.rollback();
                            }
                        }
                    }
                    catch (SQLException ignore) {}
                }

                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException ignore){}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore){}
                }
            }
    }

	public static void saveAssemblyLot(DBTransaction dbTransaction,String lotId,AssemblyDataEntryForm assembly) throws ScalarException{
		Connection conn = null;
		PreparedStatement pstmt=null;
		String checkLotId ="";

		try{

		 	if(dbTransaction == null) {
            	conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

		  	checkLotId=getAssemblyLotId(lotId);
			String lc					=assembly.getLc();
			String packageType			=assembly.getPackageType();
			String packageSize			=assembly.getPackageSize();
			String diceDiscrepancyIn	=assembly.getDiceDiscrepancyIn();
			String diceDiscrepancyOut   =assembly.getDiceDiscrepancyOut();
			String diceDiscrepancyYield	=assembly.getDiceDiscrepancyYield();
			String diceInspectIn		=assembly.getDiceInspectIn();
			String diceInspectOut		=assembly.getDiceInspectOut();
	 		String diceInspectYield		=assembly.getDiceInspectYield();
			String preMouldIn			=assembly.getPreMouldIn();
			String preMouldOut			=assembly.getPreMouldOut();
			String preMouldYield		=assembly.getPreMouldYield();
			String icFinishedIn			=assembly.getIcFinishedIn();
			String icFinishedOut		=assembly.getIcFinishedOut();
			String icFinishedYield		=assembly.getIcFinishedYield();
			String invYield				=assembly.getInvYield();
			String actYield				=assembly.getActYield();
			String asyYield				=assembly.getAsyYield();
			String quantityIn			=assembly.getQuantityIn();
			String quantityOut			=assembly.getQuantityOut();
			String dateIn				=assembly.getDateIn();
			String dateOut				=assembly.getDateOut();

			if(quantityIn.equals(""))
            quantityIn = null;
			if(dateIn.equals(""))
            dateIn = null;
			if(quantityOut.equals(""))
            quantityOut = null;
			if(dateOut.equals(""))
            dateOut = null;
			//System.out.println("dateOut "+dateOut);

	  			if(checkLotId.equals("exists")){

				//System.out.println("In Exists");
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.assemblyLotUpdate"));
				pstmt.setString(1,packageType);
				pstmt.setString(2,packageSize);
				pstmt.setString(3,lc);
				pstmt.setString(4,dateIn);
				pstmt.setString(5,dateOut);
				pstmt.setString(6,diceDiscrepancyIn);
				pstmt.setString(7,diceDiscrepancyOut);
				pstmt.setString(8,diceDiscrepancyYield);
				pstmt.setString(9,diceInspectIn);
				pstmt.setString(10,diceInspectOut);
				pstmt.setString(11,diceInspectYield);
				pstmt.setString(12,preMouldIn);
				pstmt.setString(13,preMouldOut);
				pstmt.setString(14,preMouldYield);
				pstmt.setString(15,icFinishedIn);
				pstmt.setString(16,icFinishedOut);
				pstmt.setString(17,icFinishedYield);
				pstmt.setString(18,invYield);
				pstmt.setString(19,actYield);
				pstmt.setString(20,asyYield);
				pstmt.setString(21,quantityIn);
				pstmt.setString(22,quantityOut);
				pstmt.setString(23,lotId);
				pstmt.executeUpdate();

		  	}else{
			  	//System.out.println("In Insert");
			  	pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.assemblyLotInsert"));

			  	pstmt.setString(1,packageType);
			  	pstmt.setString(2,packageSize);
			  	pstmt.setString(3,lc);
			  	pstmt.setString(4,dateIn);
			  	pstmt.setString(5,dateOut);
			  	pstmt.setString(6,diceInspectIn);
			  	pstmt.setString(7,diceInspectOut);
			  	pstmt.setString(8,diceInspectYield);
			  	pstmt.setString(9,diceInspectIn);
			  	pstmt.setString(10,diceInspectOut);
			  	pstmt.setString(11,diceInspectYield);
			  	pstmt.setString(12,preMouldIn);
			  	pstmt.setString(13,preMouldOut);
			  	pstmt.setString(14,preMouldYield);
			  	pstmt.setString(15,icFinishedIn);
			  	pstmt.setString(16,icFinishedOut);
			  	pstmt.setString(17,icFinishedYield);
			  	pstmt.setString(18,invYield);
			  	pstmt.setString(19,actYield);
			  	pstmt.setString(20,asyYield);
				pstmt.setString(21,lotId);
				pstmt.setString(22,quantityIn);
				pstmt.setString(23,quantityOut);

				//System.out.println("lotId "+lotId);

			  	pstmt.executeUpdate();
			}
		 }catch (Exception sqle) {
            sqle.printStackTrace();
			System.out.println("Sql exception ");
            throw new ScalarException ("Could not save Assembly Lot from database", sqle);
        }finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
            	try {
                	if (conn != null) {
						//System.out.println("just before commit");
                        conn.commit();
                    }

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
	}

    public static void saveAssemblyLot (DBTransaction dbTransaction, Iterator assemblyLotIterator) throws ScalarException
    {
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        boolean success 		= false;

        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.saveAssemblyLotDataEntry"));

            AssemblyLot assemblyLot = null;


            while( assemblyLotIterator.hasNext() )
            {
                assemblyLot = (AssemblyLot) assemblyLotIterator.next();

                pstmt.setString (1, assemblyLot.getDeviceName());
                pstmt.setString (2, assemblyLot.getLotNumber());
                pstmt.setString (3, assemblyLot.getPONumber() );
                pstmt.setString (4, assemblyLot.getPackageType());
                pstmt.setString (5, assemblyLot.getLeadCount());
                pstmt.setString (6, assemblyLot.getDateIn());
                pstmt.setString (7, assemblyLot.getDateOut());
                pstmt.setString (8, null);
                pstmt.setString (9, null);
                pstmt.setString (10, null);
                pstmt.setString (11, assemblyLot.getDiceInspectIn());
                pstmt.setString (12, assemblyLot.getDiceInspectOut());
                pstmt.setString (13, assemblyLot.getDiceInspectYield());
                pstmt.setString (14, null);
                pstmt.setString (15, null);
                pstmt.setString (16, null);
                pstmt.setString (17, assemblyLot.getIcFinishedIn());
                pstmt.setString (18, assemblyLot.getIcFinishedOut());
                pstmt.setString (19, assemblyLot.getIcFinishedYield());
                pstmt.setString (20, assemblyLot.getInvYield());
                pstmt.setString (21, assemblyLot.getAsyYield());
                pstmt.setString (22, assemblyLot.getQtyIn());
                pstmt.setString (23, assemblyLot.getQtyOut());
                pstmt.setString (24, assemblyLot.getVendorName());
				pstmt.setString (25, assemblyLot.getVendorLotNumber());
				pstmt.setString (26, assemblyLot.getPackageSize());
                pstmt.setString (27, assemblyLot.getDateCode());
                pstmt.setString (28, assemblyLot.getComments());
                /*pstmt.setString (29, assemblyLot.getPartialshipQtyIn() );
				pstmt.setString (30, assemblyLot.getPartialshipQtyOut() );
				pstmt.setString (31, assemblyLot.getPartialShipDateOut() );*/

                pstmt.addBatch();
            }

            pstmt.executeBatch();

            pstmt.close();
            pstmt = null;
            pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.dataEntry.assembly.closeAssemblyRelease") );
            pstmt.setString( 1, assemblyLot.getLotNumber() );
            pstmt.execute();

            success = true;
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Assembly Lot Information into database", sqle);
        }

        finally
        {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

//modified by Vasanth April 26, 2003

	public static void saveInventoryLot(DBTransaction dbTransaction,String lotId, String lotNumber, String dateIn,String dateOut,String quantityIn,String quantityOut) throws ScalarException{
		Connection conn = null;
		PreparedStatement pstmt=null;
		String checkLotId ="";

		try{

		 	if(dbTransaction == null) {
            	conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

		  	checkLotId=getInventoryLotId(lotId);


		  	if(checkLotId.equals("exists")){

				//System.out.println("In Exists");
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.inventoryLotUpdateForfinishedGoods"));
				pstmt.setString(1,dateIn);
				pstmt.setString(2,dateOut);
				pstmt.setString(3,quantityIn);
				pstmt.setString(4,quantityOut);
				pstmt.setString(5,lotNumber);
				pstmt.setString(6,lotId);
				pstmt.executeUpdate();

		  	}

			/*else{
			  	System.out.println("In Insert");
			  	pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.inventoryLotInsertForFinishedGoods"));

			  	pstmt.setString(1,lotId);
			  	pstmt.setString(2,dateIn);
			  	pstmt.setString(3,dateOut);
			  	pstmt.setString(4,quantityIn);
			  	pstmt.setString(5,quantityOut);

			  	pstmt.executeUpdate();
			}*/

		 }catch (Exception sqle) {
            sqle.printStackTrace();
			System.out.println("Sql exception ");
            throw new ScalarException ("Could not save FabProcess SetUp from database", sqle);
        }finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
            	try {
                	if (conn != null) {
						//System.out.println("just before commit");
                        conn.commit();
                    }

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
	}


	// For saving Lots
	// Added by Vasanth April, 22, 2003

	public static void saveInventoryLot(DBTransaction dbTransaction,String[] updatingNewLotNumber, String[] updatingLotId,String[] updatingDateIn,String[] updatingQuantityIn, String[] testQuantity, String[] rejectedQty, String[] dateCodes) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt=null;
		String checkLotId ="";
		//System.out.println("Lot ID is " + updatingLotId[0]);
		//System.out.println("Lot No is " + updatingNewLotNumber[0]);
		checkLotId=getInventoryLotId(updatingLotId[0]);

		try
		{
			if(dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			if(checkLotId.equals("exists"))
			{
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.inventoryLotUpdate"));
                //Commented by KumaraSwamy on 16th Sep, 2004.
				//if(!updatingQuantityIn[0].equals("") && !updatingDateIn[0].equals(""))
				//{
					pstmt.setString(1, updatingDateIn[0]);
					pstmt.setString(2, testQuantity[0]);
					pstmt.setString(3, updatingLotId[0]);
					pstmt.setString(4, updatingNewLotNumber[0].trim());
					pstmt.setString(5, updatingQuantityIn[0]);
					pstmt.setString(6, rejectedQty[0]);
                    pstmt.setString(7, dateCodes[0]);
                //}
				pstmt.executeUpdate();
			}
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			System.out.println("Sql exception ");
			throw new ScalarException ("Could not save FabProcess SetUp from database", sqle);
		}
		finally
		{
			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						conn.commit();
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}

			if (pstmt != null)
			{
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
	}


//  ends here... Vasanth






	public static void saveTestLot(DBTransaction dbTransaction,String lotId,TestDataEntryForm test,ArrayList binList) throws ScalarException{
		Connection conn = null;
		PreparedStatement pstmt=null;
		String checkLotId ="";
		int binInfo = 0;
		String binNumber ="";
		String binQty = "";

		try{


			if(dbTransaction == null) {
            	conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

			BinQuantity binQuantity = null;


		  	checkLotId=getTestLotId(lotId);
			String packageType				=	test.getPackageType();
			String scheduleNo               =	test.getScheduleNo();
			String recipeId					=	test.getRecipeId();
			String quantityIn				=	test.getQuantityIn();
			String quantityOut				=	test.getQuantityOut();
			String quantityReject			=	test.getQuantityReject();
			String dateIn					=	test.getDateIn();
			String dateOut					=	test.getDateOut();
			String yield					=   test.getYield();
			String [] binQuantitys          =   test.getBinQuantity();

			if(quantityIn.equals(""))
            quantityIn = null;
			if(dateIn.equals(""))
            dateIn = null;
			if(quantityOut.equals(""))
            quantityOut = null;
			if(dateOut.equals(""))
            dateOut = null;
			//System.out.println("dateOut "+dateOut);

			if(binQuantitys==null)
				binInfo = 1;

			//System.out.println("binInfo "+binInfo);

		  	if(checkLotId.equals("exists")){

				//System.out.println("In Exists");
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.testLotUpdate"));
				pstmt.setString(1,packageType);
				pstmt.setString(2,scheduleNo);
				pstmt.setString(3,recipeId);
				pstmt.setString(4,dateIn);
				pstmt.setString(5,dateOut);
				pstmt.setString(6,quantityIn);
				pstmt.setString(7,quantityOut);
				pstmt.setString(8,quantityReject);
				pstmt.setInt(9,binInfo);
				pstmt.setString(10,yield);
				pstmt.setString(11,lotId);
				pstmt.executeUpdate();
				pstmt.close();

				if(binInfo==0){

					for(int i=0;i<binQuantitys.length;i++){
					binQuantity =(BinQuantity)binList.get(i);
					binNumber = binQuantity.getBinNumber();
					binQty    = binQuantitys[i];
					pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.binLotUpdate"));
					pstmt.setString(1,binQty);
					pstmt.setString(2,lotId);
					pstmt.setString(3,binNumber);
					pstmt.executeUpdate();
					pstmt.close();
				}

				}



		  	}else{
			  	//System.out.println("In Insert");
			  	pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.testLotInsert"));

			  	pstmt.setString(1,lotId);
			  	pstmt.setString(2,packageType);
				pstmt.setString(3,scheduleNo);
				pstmt.setString(4,recipeId);
				pstmt.setString(5,dateIn);
				pstmt.setString(6,dateOut);
				pstmt.setString(7,quantityIn);
				pstmt.setString(8,quantityOut);
				pstmt.setString(9,quantityReject);
				pstmt.setInt(10,binInfo);
				pstmt.setString(11,yield);

			   	pstmt.executeUpdate();
				pstmt.close();

				if(binInfo==0){

					for(int i=0;i<binQuantitys.length;i++){
					binQuantity =(BinQuantity)binList.get(i);
					binNumber = binQuantity.getBinNumber();
					binQty    = binQuantitys[i];
					pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.binLotInsert"));
					pstmt.setString(1,lotId);
					pstmt.setString(2,binNumber);
					pstmt.setString(3,binQty);
					pstmt.executeUpdate();
					pstmt.close();
				}

				}





			}
		 }catch (Exception sqle) {
            sqle.printStackTrace();
			System.out.println("Sql exception ");
           throw new ScalarException ("Could not save Test Lot to database", sqle);
        }finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction

            	if (dbTransaction == null) {
            	try {
                	if (conn != null) {
						//System.out.println("just before commit");
                        conn.commit();
                    }

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
	}

	public static void saveTestLot (DBTransaction dbTransaction, ArrayList testLotList, String userName, String testType) throws ScalarException
	{
		if( testLotList == null ) return;

		Connection conn 		= null;
		CallableStatement cstmt = null;
		PreparedStatement pstmt = null;
		boolean success 		= false;

		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			// Step 1

			cstmt = conn.prepareCall( SqlQueryMappings.get( "adaptor.saveTestLotDataEntry" ) );

			TestLot testLot = null;

			Iterator testLotIterator = testLotList.iterator();

			while( testLotIterator.hasNext() )
			{
				testLot = (TestLot) testLotIterator.next();

				cstmt.setString (1, testLot.getPartNumber());
				cstmt.setString (2, testLot.getLotNumber());
				cstmt.setString (3, testLot.getPoNumber() );
				cstmt.setString (4, testLot.getPackageType());
				cstmt.setString (5, testLot.getDateIn());
				cstmt.setString (6, testLot.getDateOut());
				cstmt.setString (7, testLot.getQtyIn());
				cstmt.setString (8, testLot.getQtyOut());
				cstmt.setString (9, testLot.getQtyReject());
				cstmt.setString (10, testLot.getYield());
				cstmt.setString (11, testLot.getStatus());
				cstmt.setString (12, testLot.getVendorName());
				cstmt.setString (13, testType);
				cstmt.setString (14, testLot.getDateCode());
				cstmt.setString (15, testLot.getVendorLot());
				cstmt.setString (16, testLot.getComments());
				/*cstmt.setString (17, testLot.getPartialshipQtyIn() );
				cstmt.setString (18, testLot.getPartialshipQtyOut() );
				cstmt.setString (19, testLot.getPartialShipDateOut() );*/

				cstmt.addBatch();
			}

			cstmt.executeBatch();

			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore){}
			}
			// STEP 2
			// Save Bin Quantity Information
			pstmt = conn.prepareStatement( SqlQueryMappings.get( "adaptor.createTestBinsDataEntry" ) );
			testLotIterator = testLotList.iterator();

			while( testLotIterator.hasNext() )
			{
				testLot = (TestLot) testLotIterator.next();
				LinkedHashMap binQtyMap = testLot.getBinQty();
				if( ( binQtyMap != null ) && ( binQtyMap.isEmpty() == false ) )
				{
					Iterator keyIt = binQtyMap.keySet().iterator();
					while( keyIt.hasNext() )
					{
						String binNumber = (String)keyIt.next();
						pstmt.setString (1, testLot.getPartNumber());
						pstmt.setString (2, testLot.getLotNumber());
						pstmt.setString (3, binNumber);
						pstmt.setString (4, (String)binQtyMap.get(binNumber)); //Bin Qty
						pstmt.setString (5, testLot.getVendorName());
						pstmt.setString (6, testType);
						pstmt.addBatch();
					}

					pstmt.executeBatch();
				}
			}

            pstmt.close();
            pstmt = null;
            pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.dataEntry.assembly.closeAssemblyRelease") );
            pstmt.setString( 1, testLot.getLotNumber() );
            pstmt.execute();

			success = true;
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Test Lot Information into database", sqle);
		}
		finally
		{
			if (dbTransaction == null) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore){}
			}

			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}


	//Added by Bhaskar on 6th Dec 2003
	public static void saveInventoryLot (DBTransaction dbTransaction, Iterator inventoryLotIterator, String inventoryType, String vendorAdaptorName) throws ScalarException
	{

		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean success = false;

		try
		{
			// Get the connection from regular datasource if its not in a manual transaction
			if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			//Bhaskar commented this line on 10 December 2003
			//cstmt = conn.prepareCall (SqlQueryMappings.get ("adaptor.saveInventoryLot"));
			pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.saveInventoryLot.cavium"));
			InventoryLot inventoryLot = null;
			while (inventoryLotIterator.hasNext())
			{
				inventoryLot = (InventoryLot) inventoryLotIterator.next();
				pstmt.setString (1, inventoryLot.getPartNumber());
				pstmt.setString (2, inventoryLot.getLotNumber());
				pstmt.setString (3, inventoryLot.getQtyIn());
				pstmt.setString (4, inventoryLot.getQtyOut());

				//pstmt.setString (5, inventoryLot.getDateIn());
				//pstmt.setString (6, inventoryLot.getDateOut());

                pstmt.setDate (5, StringUtils.getSQLDate(inventoryLot.getDateIn() , null));
				pstmt.setDate (6, StringUtils.getSQLDate(inventoryLot.getDateOut(), null));

				pstmt.setString (7, inventoryLot.getType());
				pstmt.setString (8, inventoryLot.getVendorName());
				pstmt.setString (9, inventoryLot.getDateCode());
                pstmt.setString (10, inventoryLot.getVendorLot());
                pstmt.setString (11, inventoryLot.getComments());
                pstmt.setString ( 12, inventoryLot.getWaferQtyIn() );
                pstmt.setString ( 13, inventoryLot.getWaferId() );
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			//Bhaskar commented the above stmt and added the stmt below on 10 December 2003
			//cstmt.execute();
            if ( inventoryLot != null && Constants.LOCATION_TYPE_CLASS_STORE.equalsIgnoreCase( inventoryLot.getType() ) )
            {
                pstmt.close();
                pstmt = null;
                pstmt = conn.prepareStatement( SqlQueryMappings.get("operations.dataEntry.assembly.shipAssemblyRelease") );
                pstmt.setString( 1, inventoryLot.getLotNumber() );
                pstmt.execute();
            }


			success = true;
		}

		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Inventory Lot Information into database", sqle);
		}

		finally
		{
			// Commit/Rollback here it self -- if its not in a manual transaction
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						if (success == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {ignore.printStackTrace();}
				catch (Exception e){e.printStackTrace();}
			}

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}


			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
		}
	}


	//added by Mallikarjun Ankati
	public static String createLot(DBTransaction dbTransaction,
							String processStepID,
							String locationID,
							String lotNumber,
							String purchaseOrderNo,
							String vendorLotNo,
							String dateIn,
							String quantity,
							String createdBy
						 ) throws ScalarException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String lotId = "";

		try{

			if(dbTransaction == null) {
            		conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}
			ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.lotTableInsert"));
			ps.setString(1, processStepID);
			ps.setString(2, locationID);
			ps.setString(3, lotNumber);
			ps.setString(4, "Running");
			ps.executeUpdate();

			ps.close();
			ps =  null;

			ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.upload.readIdentity"));
			rs = ps.executeQuery();

			if(rs.next() != false){
				lotId = rs.getInt("docID")+"";
			}

			ps.close();
			ps = null;

			ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.lot.lotCostUpdate"));
			ps.setString(1, processStepID);
			ps.setString(2, locationID);
			ps.setString(3, quantity);
			ps.setString(4, lotId);
			ps.executeUpdate();

			ps.close();
			ps = null;

			ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.fabLotInsert"));
			ps.setString(1, lotId);
			ps.setString(2, purchaseOrderNo);
			ps.setString(3, vendorLotNo);
			ps.setString(4, dateIn);
			ps.setString(5, quantity);
			ps.setString(6, createdBy);

			ps.executeUpdate();

		}catch (Exception sqle) {
            sqle.printStackTrace();
			throw new ScalarException ("Could not Create Lot", sqle);
        }finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
            	try {
                	if (conn != null) {
						//System.out.println("just before commit");
                        conn.commit();
                    }

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException ignore){}
            }
            if(rs != null){
            	 try { rs.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}

		return lotId;
	}

	//added by Mallikarjun Ankati
	public static String insertLot(DBTransaction dbTransaction, String processStepID,String locationID,String lotNumber, String lotQuantity) throws ScalarException {
		Connection conn = null;
		PreparedStatement ps = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		String lotId = "";

		try{

			if(dbTransaction == null) {
            			conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

			ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.lotTableInsert"));
			ps.setString(1, processStepID);
			ps.setString(2, locationID);
			ps.setString(3, lotNumber);
			ps.setString(4, "Running");
			ps.executeUpdate();

			ps.close();
			ps =  null;

			ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.upload.readIdentity"));
			rs = ps.executeQuery();

			if(rs.next() != false){
				lotId = rs.getInt("docID")+"";
			}

                        //System.out.println("insertLot() Lot ID = "+lotId);
                        //System.out.println("\t processStepID = "+ processStepID);
                        //System.out.println("\t locationID = "+locationID);
                        //System.out.println("\t lotQuatity = "+lotQuantity);

			ps.close();
			ps =  null;

			cs = conn.prepareCall(SqlQueryMappings.get ("operations.process.lot.costUpdateProc"));
			cs.setString(1, processStepID);
			cs.setString(2, locationID);
			cs.setString(3, lotNumber);
			cs.setString(4, lotQuantity);
			cs.setString(5, lotId);

			cs.executeUpdate();

		}catch (Exception sqle) {
            		sqle.printStackTrace();
			throw new ScalarException ("Could not Insert Lot", sqle);
        }finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
            	try {
                	if (conn != null) {
				//System.out.println("just before commit");
                        	conn.commit();
                    	}
                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
            if(rs != null){
            	 try { rs.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
            if (cs != null) {
                try { cs.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
	}

	return lotId;
      }


	//added by Mallikarjun Ankati 08/29/2002
	public static String putLotInNextStep(DBTransaction dbTransaction, String lotId, String lotNumber) throws ScalarException {
		Connection conn = null;
		PreparedStatement ps = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		String error_code = "";
		String returnLotId = "";

		try{
			if(dbTransaction == null) {
            			conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

			cs = conn.prepareCall(SqlQueryMappings.get ("operations.process.lot.putLotInNextStep"));
			cs.setString(1, lotId);
			cs.setString(2, lotNumber);
			cs.registerOutParameter(3, java.sql.Types.VARCHAR);
			cs.registerOutParameter(4, java.sql.Types.INTEGER);

			cs.executeQuery();

			error_code = cs.getString(3);
			returnLotId = cs.getInt(4)+"";

		}catch (Exception sqle) {
            	sqle.printStackTrace();
			throw new ScalarException ("Could not Insert Lot", sqle);
        	}finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
            	if (dbTransaction == null) {
            		try {
                			if (conn != null) {
						//System.out.println("just before commit");
                        		conn.commit();
                    		}
                		}catch (SQLException ignore) {}
            	}

            	if (ps != null) {
                		try { ps.close(); } catch (SQLException ignore){}
            	}
            	if(rs != null){
            		try { rs.close(); } catch (SQLException ignore){}
            	}
            	if (cs != null) {
                		try { cs.close(); } catch (SQLException ignore){}
            	}
            	if (conn != null) {
                		try { conn.close(); } catch (SQLException ignore){}
            	}
		}

		return (error_code+"##"+returnLotId);
	}


	//added my Mallikarjun Ankati
	public static void updateLotStatus(DBTransaction dbTransaction, int lotID, String status) throws ScalarException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{

			if(dbTransaction == null) {
            	conn = DataSource.getConnection();
			}else {
				conn = dbTransaction.getConnection();
			}

            if( "confirm".equals( status ) )
            {
                ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.lotTableHoldUpdate"));
                ps.setInt(1, lotID);
            }
            else
            {
			    ps = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.lotTableStatusUpdate"));
                ps.setString(1, status);
                ps.setInt(2, lotID);
            }


			ps.executeUpdate();

		}catch (Exception sqle) {
            sqle.printStackTrace();
			throw new ScalarException ("Could not Update Lot_Table", sqle);
        }finally{
			// Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
            	try {
                	if (conn != null) {
				//System.out.println("just before commit");
                        	conn.commit();
                    	}

                }catch (SQLException ignore) {System.out.println("Sql exception ");}
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException ignore){}
            }
            if(rs != null){
            	 try { rs.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
	}


    // Balaram.... Feb 26, 2003
	// Modified by Vasanth.... April 26, 2003
	// Modified by Vasanth.... September 25, 2003 for implementing the adaptor procedure when creating a new lot
	// code rechanged to original
    public static void createInventoryLotForFinishedGoods (DBTransaction dbTransaction, String processStepId, String locationId, String lotNumber, String qtyIn, String qtyOut, String dateIn, String dateOut, String dateCode) throws ScalarException {

        Connection conn = null;
        CallableStatement cstmt = null;

        try {
            if(dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall (SqlQueryMappings.get ("operations.process.lot.createInventoryLotForFinishedGoods"));
            cstmt.setString (1, processStepId);
            cstmt.setString (2, locationId);
            cstmt.setString (3, lotNumber);
            cstmt.setString (4, qtyIn);
            cstmt.setString (5, qtyOut);
            cstmt.setString (6, dateIn);
            cstmt.setString (7, dateOut);
            cstmt.setString (8, dateCode);
            cstmt.executeUpdate();
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save create inventory lot for processStepID: " + processStepId + ", locationID: " + locationId + ", lotNumber: " + lotNumber, sqle);
        }

        finally {

            if (dbTransaction == null) {
            	try {
                    if (conn != null) {
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }


            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    // for creating a new serial number by
	// Vasanth April, 23, 2003

     public static void createInventoryLot (DBTransaction dbTransaction, String processStepId, String locationId, String[] lotNumber, String[] qtyIn, String[] dateIn, String[] testQty, String[] rejectedQty, String[] dateCode ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {

            if(dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.lot.createInventoryLot"));


            for (int i=0;i<lotNumber.length;i++){
				if ( lotNumber[i].trim().length() == 0 )
					continue;
                //Commented by KumaraSwamy on 16th Sep, 2004.
				//if(!qtyIn[i].equals("") && !dateIn[i].equals("")) {
					pstmt.setString (1, processStepId);
					pstmt.setString (2, locationId);
					pstmt.setString (3, lotNumber[i].trim());
					pstmt.setString (4, testQty[i]);
					pstmt.setString (5, dateIn[i]);
					pstmt.setString (6, qtyIn[i]);
					pstmt.setString (7, rejectedQty[i]);
                    pstmt.setString (8, dateCode[i]);    
                    pstmt.addBatch();
				//}
            }
            pstmt.executeBatch();
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save create inventory lot for processStepID: " + processStepId + ", lotNumber: " + lotNumber[0], sqle);
        }

        finally
        {
            if (dbTransaction == null)
            {
            	try
            	{
                    if (conn != null)
                    {
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    /*This method is added by Bhaskar on 6 December 2003 in order to the lotId
     *get and lotNumber for a specific process Id and location id.
     *This method is called in Some Data Entry Action classes.
     */


	public static String getLotIdInDataEntry ( DBTransaction dbTransaction,
						String processstep_id, String location_id,
						String lot_number ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		String lotId ="";
		PreparedStatement pstmt = null;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement( SqlQueryMappings.get ("operations.dataentry.getLotIdInDataEntry") );
			pstmt.setString ( 1, lot_number );
			pstmt.setInt ( 2, Integer.parseInt (processstep_id.trim()) );
			pstmt.setInt ( 3, Integer.parseInt (location_id.trim()) );

			rs = pstmt.executeQuery();
			while(rs.next())
			{
				lotId = ""+rs.getInt(1);
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException("In getting Lot Id ", sql);
		}
		finally
		{
			// Commit or Rollback here it self -- if its not in a manual transaction
			/*if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						if (status == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}*/
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return lotId;
	}




    public static ArrayList getInventoryLotIdsAndNumbers ( String processStepId,
        String locationId ) throws ScalarException
    {
        ArrayList lotIds = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.lot.getLotNumbersAndIds"));

            pstmt.setInt (1, Integer.parseInt(processStepId.trim()));
            pstmt.setInt (2, Integer.parseInt(locationId.trim()));

            rs = pstmt.executeQuery();
            Lot lots = null;
            while ( rs.next () )
            {
                lots = new Lot();
                lots.setLotId ( rs.getInt ( 1 ) +"");
                lots.setLotNumber ( rs.getString ( 2 ) );
                lotIds.add ( new org.apache.struts.util.LabelValueBean (
                                lots.getLotNumber (), lots.getLotId () ) );
            }
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not fetch the lots for processStepID: " + processStepId + ", locationID: " + locationId , sqle);
        }

        finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return lotIds;
    }

    /*
     *This method is added by Bhaskar on 6 August 2003 in order to populate the
     *selected lot's quantity in, quantity out, date in, date out values in Data Entry.
     *This is called in InventoryDataEntryAction.java
     */
    public static ArrayList getCurrentLotDetails (String lotId) throws ScalarException
    {
        ArrayList lotDetails = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.lot.getCurrentLotDetails"));
            pstmt.setInt (1, Integer.parseInt(lotId.trim()));

            rs = pstmt.executeQuery();
            Lot lots = null;
            while ( rs.next () )
            {
                lots = new Lot();
                lots.setLotId ( rs.getInt ( "lot_id" ) +"");
                lots.setQuantityIn (rs.getInt ( "il_qty_in" )+"" );
                lots.setQuantityOut (rs.getInt ( "il_qty_out" )+"" );
                lots.setDateIn ( rs.getString ( "il_date_in" )+"" );
                lots.setDateOut ( rs.getString ( "il_date_out" )+"" );
                lotDetails.add ( lots );
            }
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not fetch the lots for lotID: " + lotId , sqle);
        }

        finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return lotDetails;
    }

    /*
     *This method is added by Bhaskar on 6 August 2003 in order to populate the
     *selected lot's quantity in, quantity out, date in, date out values in Data Entry.
     *This is called in InventoryDataEntryAction.java
	 *Modified by Vasanth 08/13/2003
     */
    public static boolean checkWhetherLotExists (String newLotNo, String processStepId, String locationId) throws ScalarException
    {
        boolean rowExists = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ( "operations.process.lot.checkWhetherLotExists" ));
            pstmt.setString (1, newLotNo.trim());
			pstmt.setInt(2, Integer.parseInt(processStepId.trim()));
			pstmt.setInt(3, Integer.parseInt(locationId.trim()));

            rs = pstmt.executeQuery();
            if ( rs.next () )
            {
                rowExists = true;
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not fetch the lot_number for lotID: " + newLotNo , sqle);
        }

        finally {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return rowExists;
    }
	//Bhaskar added this new method on 27 september inorder to get the existing
	//lot numbers and check for same lot number before insertion or updation of
	//the same.
	public static ArrayList getInventoryLotNumbers ( String newLotNumbers[], String processStepId, String locationId ) throws ScalarException
	{
		ArrayList lotIds = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = DataSource.getConnection();
			String str = SqlQueryMappings.get("operations.process.lot.checkInventoryLotNumber");
			StringBuffer sqlQry = new StringBuffer( str );
			for (int i = 0; i < newLotNumbers.length; i++ )
			{
				sqlQry.append("lot_number = ? or ");
			}
			sqlQry.delete(sqlQry.length()-3, sqlQry.length());
			sqlQry.append(") and processStep_id = ? and location_id = ? ");
			//pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.lot.getLotNumbersAndIds"));
			pstmt = conn.prepareStatement (sqlQry.toString());

			int j = 1;
			for (int i = 0; i < newLotNumbers.length; i++ )
			{
				pstmt.setString (j, newLotNumbers[i].trim() );
				j++;
			}
			pstmt.setInt ( j, Integer.parseInt ( processStepId ) );
			pstmt.setInt ( ++j, Integer.parseInt ( locationId ) );
			rs = pstmt.executeQuery();
			while ( rs.next () )
			{
				lotIds.add ( rs.getString ( 1 ) );
			}
		}

		catch (Exception sqle) {
			//sqle.printStackTrace();
			throw new ScalarException ("Could not fetch the lot_numbers ", sqle);
		}

		finally {
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		return lotIds;
	}

    public static int getCSLNTQuantityShiped (DBTransaction dbTransaction, String cusShip_Id ) throws ScalarException
	{
		int qtyPulled = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
            if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            pstmt = conn.prepareStatement (SqlQueryMappings.get ( "operations.process.lot.CSLNT_QuantityShipped" ));
			pstmt.setInt ( 1, Integer.parseInt ( cusShip_Id ) );
			rs = pstmt.executeQuery();
			while ( rs.next () )
			{
				qtyPulled =  rs.getInt ( "QTY_SHIPPED" ) ;
			}
		}

		catch (Exception sqle) {
			//sqle.printStackTrace();
			throw new ScalarException ("Could not fetch the lot_numbers ", sqle);
		}

		finally {
            if (rs != null) {
				try { rs.close(); } catch (SQLException ignore){}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		return qtyPulled;
	}


    public static int getCSLNTQuantityPulled (DBTransaction dbTransaction, String lotId ) throws ScalarException
	{
		int qtyPulled = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
            if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            pstmt = conn.prepareStatement (SqlQueryMappings.get ( "operations.process.lot.CSLNT_QuantityPulled" ));
			pstmt.setInt ( 1, Integer.parseInt ( lotId ) );
			rs = pstmt.executeQuery();
			while ( rs.next () )
			{
				qtyPulled =  rs.getInt ( "qty_pulled" ) ;
			}
		}

		catch (Exception sqle) {
			//sqle.printStackTrace();
			throw new ScalarException ("Could not fetch the lot_numbers ", sqle);
		}

		finally {
            if (rs != null) {
				try { rs.close(); } catch (SQLException ignore){}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore){}
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		return qtyPulled;
	}



    public static void main(String[] args) throws ScalarException{
        String lotId = insertLot(null, "1755", "282", "MSI000011", "4100");
        System.out.println("Lot ID = "+ lotId);
    }


	// Added by Bhaskar on 08 December 2003
	public static void changeLotStatusToCloseByLotId (DBTransaction dbTransaction, String lotId) throws ScalarException {

		Connection conn = null;
		CallableStatement cstmt = null;
		boolean success = false;

		try {
			// Get the connection from regular datasource if its not in a manual transaction
			if (dbTransaction == null) {
				conn = DataSource.getConnection();
			}
			else {
				conn = dbTransaction.getConnection();
			}
			// update the status
			cstmt = conn.prepareCall(SqlQueryMappings.get ("operations.dataentry.changeLotStatusToCloseByLotId"));
			cstmt.setInt (1, Integer.parseInt(lotId.trim()));
			cstmt.execute();
			success = true;
		}

		catch (Exception sqle) {
			sqle.printStackTrace();
			throw new ScalarException ("Could not save Assembly Lot Information into database", sqle);
		}

		finally {

			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null) {
				try {
					if (conn != null) {
						if (success == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if (cstmt != null) {
				try { cstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
	}


	/*
	Bhaskar modified this method on 3 Mar 2004 inorder to populate the gross die wafer qty and die size from fab product
	setup onto the fab/sort data entry screens.Only 2 values in a string array are returned. But while using this method
	in sort data entry make sure that u take the second element in the string array as the first element is not needed
	in sort data entry.
	*/
	public static String[] getGrossDieWafer ( DBTransaction dbTransaction,
						String processstep_id ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		String grossDie = "";
		String dieSize  = "";
		/*
		The String array returnArray declared and initialized below is of length=2. This was intentionally initialized with
		blank values. The reason for doing is when there are no elements present for Gross Die Wafer and Die Size then the
		loop is never entered and the elements remain uninitialized and there is a scope of null gettiing displayed in the
		page. So the values are set to default of blank values. Bhaskar on 13 May 2004
		*/
		String returnArray[] = {"",""};
		PreparedStatement pstmt = null;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement( SqlQueryMappings.get ("operations.dataentry.getGrossDieWafer") );
			pstmt.setString ( 1, processstep_id );
			rs = pstmt.executeQuery();
			while ( rs.next() )
			{
				String tempKey = rs.getString ("parameter_key");
				if ( tempKey.equalsIgnoreCase( "Gross Die/Wafer" ) )
				{
					grossDie = rs.getString ( "parameter_value" );
				}
				else if ( tempKey.equalsIgnoreCase( "Die Size" ) )
				{
					dieSize = rs.getString ( "parameter_value" );
				}
			}
			returnArray[0] = grossDie;
			returnArray[1] = dieSize;
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException("Error in getting gross die wafer ", sql);
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return returnArray;
	}


	/*
	Bhaskar added this method on 4 Feb 2004 to format the yield Value to two decimals.
	*/
	public static String formatYieldToTwoDecimals ( String yieldValue )
	{
		String yield = yieldValue;
		try
		{
			if ( ( ( yield != null ) && ( yield.trim().equalsIgnoreCase( "null" ) ) ) || ( yield == null ) || ( yield.trim().length() == 0 ) || ( yield.equals("0.0") ) )
			{
				yield = "0.00";
			}
			else
			{
				yield = StringUtils.formatAmount((float)(new Double(yield).doubleValue()), "###.##");
			}
		}
		catch (Exception e )
		{
			yield = "0.00";
		}
		return yield;
	}

    public static String getPoNumber( int pOrderID ) throws ScalarException
    {
		Connection conn  = null;
		ResultSet rs     = null;
		String poNumber ="";
		PreparedStatement pstmt = null;

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( SqlQueryMappings.get ("operations.dataentry.getPONumber") );
			pstmt.setInt ( 1, pOrderID );
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				poNumber = rs.getString("Purchase_Order_Number");
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException("In getting Purchase_Order_Number ", sql);
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return poNumber;
    }


	public static String getFabProcessStepIdForSortProcessStepId ( DBTransaction dbTransaction,
						String processstep_id ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		String processStepId = "";
		PreparedStatement pstmt = null;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement( SqlQueryMappings.get ("operations.dataentry.getFabProcessStepIdForSortProcessStepId") );
			pstmt.setString ( 1, processstep_id );
			rs = pstmt.executeQuery();
			if ( rs.next())
			{
				processStepId = rs.getString ( "ProcessStep_ID" );
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException("In getting Fab ProcessStep id ", sql);
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return processStepId;
	}

	public static String getPackageTypeValue( int processStepId ) throws ScalarException
	{
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String packageType = null;
		try
		{
			conn = DataSource.getConnection ();

			pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.lot.getParameterType" ) );

			pstmt.setInt ( 1, processStepId );

			rs = pstmt.executeQuery ();

			while ( rs.next () )
			{
				packageType = "";
				try
				{
					packageType = rs.getString ( "Parameter_Value" );
					if ( ( packageType == null ) || ( packageType.trim().length() == 0 ) )
					{
						packageType = "";
					}
				}
				catch(Exception e)
				{
					packageType = "";
				}

			}
				}
		catch ( Exception sqle )
		{
			sqle.printStackTrace ();
			throw new ScalarException ( "Could not get PackageType Value from database", sqle );
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore){}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
		return packageType;
	}

    //Added by Rohan on July 28th 2004
    /**
     * Returns the lots in the states Running, Closed and Hold
     *
     * @param processStepId
     * @param locationId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getAllLots( String processStepId, String locationId ) throws ScalarException
    {
        Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ArrayList lotList = new ArrayList();
        Lot lot = null;

		try
		{
			conn = DataSource.getConnection ();
			pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.process.lot.getAllLots" ) );

			pstmt.setString( 1, processStepId );
			pstmt.setString( 2, locationId );

			rs = pstmt.executeQuery ();

			while ( rs.next () )
			{
                lot = new Lot();
                lot.setLotId( rs.getString( "Lot_ID" ) );
                lot.setLotNumber( rs.getString( "Lot_Number" ) );
                lot.setStatus( rs.getString( "Lot_Current_Status" ) );
                lotList.add( lot );
			}
        }
		catch ( Exception sqle )
		{
			sqle.printStackTrace ();
			throw new ScalarException ( "Error while retreiving the lot list", sqle );
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore){}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore){}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore){}
			}
		}
        return lotList;
    }


    public static int savePackingSlipInventoryLot ( DBTransaction dbTransaction, InventoryLot inventoryLot ) throws ScalarException
    {

        Connection conn = null;
        CallableStatement cstmt = null;
        int  newLotId = -1;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall ( SqlQueryMappings.get ("operations.packingslip.saveInventoryLot.cavium") );
            cstmt.setString (1, inventoryLot.getPartNumber());
            cstmt.setString (2, inventoryLot.getLotNumber());
            cstmt.setString (3, inventoryLot.getQtyIn());
            cstmt.setString (4, inventoryLot.getQty_balance());
            cstmt.setString (5, inventoryLot.getDateIn());
            cstmt.setString (6, inventoryLot.getDateOut());
            cstmt.setString (7, inventoryLot.getType());
            cstmt.setString (8, inventoryLot.getVendorName());
            cstmt.setString (9, inventoryLot.getDateCode());
            cstmt.setString (10, inventoryLot.getVendorLot());
            cstmt.setString (11, inventoryLot.getComments());
            cstmt.setString (12, inventoryLot.getFabLotNumber());
            cstmt.registerOutParameter(13, java.sql.Types.INTEGER );
            cstmt.executeQuery();

            newLotId =  cstmt.getInt(13);
            if( newLotId == 0 )
            {
                newLotId = -1;
            }
            success = true;
        }

        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save PS Inventory Lot Information into database", sqle);
        }

        finally
        {
            // Commit/Rollback here it self -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {ignore.printStackTrace();}
                catch (Exception e){e.printStackTrace();}
            }
            if (cstmt != null)
            {
                try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }

        return newLotId ;
    }



	/**
	 * @param lotId
	 * @return
	 */
	public static LotCorrection fetchLotInformation( String lotId )throws ScalarException
	{
		Connection conn 		= null;
		CallableStatement cstmt = null;
		ResultSet rs			= null;
		LotCorrection lotCorrection = null;

		try
		{
			conn = DataSource.getConnection();
			cstmt = conn.prepareCall ( "{ call sp_fetch_lot_info_by_lot_id ( ? ) }" );
			cstmt.setInt( 1, Integer.parseInt( lotId ) );
			rs = cstmt.executeQuery();

			if( rs.next() )
			{
				lotCorrection = new LotCorrection();
				lotCorrection.setQuantityIn( rs.getString( "qtyIn" ) );
				lotCorrection.setDateIn( rs.getString( "dateIn" ) );
				lotCorrection.setLotStatus( rs.getString( "status" ) );
				lotCorrection.setQuantityOut( rs.getString( "qtyOut" ) );
				lotCorrection.setDateOut( rs.getString( "dateOut" ) );
				lotCorrection.setLotNumber( rs.getString( "Lot_number" ) );
				lotCorrection.setDateCode( rs.getString( "Date_Code" ) );
				lotCorrection.setVendorLotNumber( rs.getString( "Vendor_Lot_Number" ) );
				lotCorrection.setLotComments( rs.getString( "Comments" ) );
				lotCorrection.setDieSize( rs.getString( "DieSize" ) );
				lotCorrection.setGrossDie( rs.getString( "Gross_Die" ) );
				lotCorrection.setHold( ( rs.getBoolean( "Hold" ) ) ? false : true );
			}
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException( "Could not fetch the Lot Information for Lot " + lotId, sqle );
		}
		finally
		{
			if( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( cstmt != null )
			{
				try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
		}

		return lotCorrection;
    }



	/**
	 * @param lotReworkForm
	 */
	public static void saveReworkLot( LotReworkForm lotReworkForm ) throws ScalarException
	{
		Connection conn 		= null;
		CallableStatement cstmt = null;
		ResultSet rs			= null;
		boolean success 		= false;
		try
		{
			conn = DataSource.getConnection();
			cstmt = conn.prepareCall ( "{ call sp_save_rework_lot( ?, ?, ?, ?, ?, ? ) }" );
			cstmt.setInt( 1, Integer.parseInt( lotReworkForm.getLotId() ) );
			cstmt.setInt( 2, Integer.parseInt( lotReworkForm.getNewProcessStepId() ) );
			cstmt.setInt( 3, Integer.parseInt( lotReworkForm.getNewLocationId() ) );
			cstmt.setInt( 4, lotReworkForm.getNewQtyIn() );
			cstmt.setDate( 5, StringUtils.getSQLDate( lotReworkForm.getNewDateIn() ) );

			//System.out.println( "Exiting Inventory FALG " + lotReworkForm.getExistingInventory() );
            if( lotReworkForm.isPrevLotExists() ){
			    cstmt.setInt( 6, lotReworkForm.getExistingInventory() );
            }
            else{
                cstmt.setInt( 6, 1 );
            }
			cstmt.execute();
			success = true;
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException( "Could not save Lot Rework information ", sqle );
		}
		finally
		{
			if( success )
			{
				try
				{
					conn.commit();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
			else
			{
				try
				{
					conn.rollback();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
			if( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( cstmt != null )
			{
				try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
		}
	}



	/**
	 * @param lotId
	 * @param processStepId
	 * @param locationId
     * @return LotCorrection
	 */
	public static LotCorrection fetchLotInformation( String lotId, String processStepId, String locationId )throws ScalarException
	{
		Connection conn 		= null;
		CallableStatement cstmt = null;
		ResultSet rs			= null;
		LotCorrection lotCorrection = null;

		try
		{
			conn = DataSource.getConnection();
			cstmt = conn.prepareCall ( "{ call sp_fetch_lot_info_by_processstep_location ( ?, ?, ? ) }" );
			cstmt.setInt( 1, Integer.parseInt( lotId ) );
			cstmt.setInt( 2, Integer.parseInt( processStepId ) );
			cstmt.setInt( 3, Integer.parseInt( locationId ) );

			rs = cstmt.executeQuery();

			if( rs.next() )
			{
				if( rs.getBoolean( "lot_exists" ) )
				{
					lotCorrection = new LotCorrection();
					lotCorrection.setQuantityIn( rs.getString( "qtyIn" ) );
					lotCorrection.setDateIn( rs.getString( "dateIn" ) );
				}
			}
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException( "Could not fetch the Lot Information for Lot " + lotId, sqle );
		}
		finally
		{
			if( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( cstmt != null )
			{
				try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
		}

		return lotCorrection;
    }



	/**
	 * @param lotReworkForm
	 */
	public static void changeLotLocation( LotReworkForm lotReworkForm ) throws ScalarException
	{
		Connection conn 		= null;
		CallableStatement cstmt = null;
		ResultSet rs			= null;
		boolean success			= false;

		try
		{
			conn = DataSource.getConnection();
			cstmt = conn.prepareCall ( "{ call sp_change_lot_location ( ?, ?, ? ) }" );
			cstmt.setInt( 1, Integer.parseInt( lotReworkForm.getLotId() ) );
			cstmt.setInt( 2, Integer.parseInt( lotReworkForm.getNewLocationId() ) );
			cstmt.setInt( 3, 1 );// This third parameter is kept for flag what to do with the previous lot As of now not implemented

			cstmt.executeUpdate();
			success = true;
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException( "Could not save the change Location for Lot " + lotReworkForm.getLotId(), sqle );
		}
		finally
		{

			if( conn != null )
			{
				try
				{
					if( success )
					{
						conn.commit();
					}
					else
					{
						conn.rollback();
					}
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}

			if( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( cstmt != null )
			{
				try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
		}

		return ;
    }



	/**
	 * @param lotReworkForm
	 */
	public static void changeLotProduct(LotReworkForm lotReworkForm) throws ScalarException
	{
		Connection conn 		= null;
		CallableStatement cstmt = null;
		ResultSet rs			= null;
		boolean success			= false;

		try
		{
			conn = DataSource.getConnection();
			cstmt = conn.prepareCall ( "{ call sp_change_lot_product ( ?, ?, ? ) }" );
			cstmt.setInt( 1, Integer.parseInt( lotReworkForm.getLotId() ) );
			cstmt.setInt( 2, Integer.parseInt( lotReworkForm.getNewProcessStepId() ) );
			cstmt.setInt( 3, lotReworkForm.getExistingInventory() );

			cstmt.executeUpdate();
			success = true;
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException( "Could not save the change Product for Lot " + lotReworkForm.getLotId(), sqle );
		}
		finally
		{

			if( conn != null )
			{
				try
				{
					if( success )
					{
						conn.commit();
					}
					else
					{
						conn.rollback();
					}
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}

			if( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( cstmt != null )
			{
				try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
			if( conn != null )
			{
				try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
			}
		}

		return ;
    }

    /**
    * @param lotId
    * @return ArrayList
    */
    public static ArrayList getAllLotDetails ( String lotId ) throws ScalarException
    {
        Connection conn 		= null;
        CallableStatement cstmt = null;
        ResultSet rs			= null;
        LotCorrection lotCorrection = null;
        ArrayList lotList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            cstmt = conn.prepareCall (" { call sp_get_all_lot_history ( ? ) }"  );
            cstmt.setInt( 1,  Integer.parseInt( lotId )  );

            rs = cstmt.executeQuery();

            while( rs.next() )
            {
                lotCorrection = new LotCorrection();
                lotCorrection.setLocationName( rs.getString( "Location" ) );
                lotCorrection.setLotNumber( rs.getString( "Lot_Number") );
                lotCorrection.setQuantityIn( rs.getString( "Qty_In" ) );
                lotCorrection.setDateIn( rs.getString( "Date_In" ) );
                lotCorrection.setLotStatus( rs.getString( "Current_Status" ) );
                lotCorrection.setQuantityOut( rs.getString( "Qty_Out" ) );
                lotCorrection.setDateOut( rs.getString( "Date_Out" ) );

                String dateIn =lotCorrection.getDateIn();
                if ( dateIn != null )
                {
                    dateIn = StringUtils.formatDate( dateIn ,"yyyy-MM-dd HH:mm:ss" ,"MM/dd/yyyy" );
                    lotCorrection.setDateIn( dateIn );
                }
                String dateOut =lotCorrection.getDateOut();
                if ( dateOut != null )
                {
                    dateOut = StringUtils.formatDate( dateOut,"yyyy-MM-dd HH:mm:ss" ,"MM/dd/yyyy" );
                    lotCorrection.setDateOut( dateOut );
                }

               lotList.add( lotCorrection );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not fetch the Lot Information for Lot " + lotId, sqle );
        }
        finally
        {
            if( rs != null )
            {
                try { rs.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
            if( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
            if( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
            }
        }

        return lotList;
    }

    /**
     * This method is used to get the Gross Die from the Fab process.<br>
     * This methods gets the TOP 1 fab processStepId from the processStepId passed.
     * @param dbTransaction
     * @param processStepId
     *
     * @return
     * @throws ScalarException
     */
    public static int getFabGrossDieFromProcessStepId( DBTransaction dbTransaction, int processStepId ) throws ScalarException
    {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        int grossDie = 0;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection ();
            }

            pstmt = conn.prepareCall ( "SELECT Parameter_Value FROM Process_Parameter WHERE Parameter_key = ? AND ProcessStep_ID = (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (?,0) WHERE PS_TYPE = ? )" );
            pstmt.setString ( 1, ParameterTypes.grossDieWafer );
            pstmt.setInt ( 2, processStepId );
            pstmt.setString ( 3, ProcessTypes.fab );

            rs = pstmt.executeQuery ();

            if ( rs.next () )
            {
                grossDie = rs.getInt( 1);
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not load Gross Die Value for the ProcessStepID", sqle );
        }

        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException ( "Could Not Commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException( "Could Not Rollback", e );
                        }
                    }
                }
            }
            if ( rs != null )
            {
                try {     rs.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( pstmt != null )
            {
                try {     pstmt.close ();       }     catch ( SQLException ignore )    {     }
            }
            if ( conn != null )
            {
                try {    conn.close ();     }     catch ( SQLException ignore )      {      }
            }
        }
        return grossDie;
    }

    /**
     * This method is used to get the lot numbers which r in TRANSIT status for the FAB ProcessStepId<br>
     * this mehtod will get the FAB ProcessStep Id from the given processStepId.
     * @param dbTransaction
     * @param processStepId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getTransitLotNumbers ( DBTransaction dbTransaction, String processStepId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset  = null;
        boolean success = false;
        ArrayList lotNumbers    = new ArrayList();
        TransitLotNumber transitLotNumber   = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement( SqlQueryMappings.get ( "operations.dataEntry.getTranisitLotNumbers" ) );
            pstmt.setString ( 1, processStepId );
            rset    = pstmt.executeQuery();
            String waferId  = null;
            while ( rset.next() )
            {
                transitLotNumber    = new TransitLotNumber();
                transitLotNumber.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                transitLotNumber.setNextLocation( rset.getString ( "TLT_NextLocation" ) );
                transitLotNumber.setVendorLotNumber( rset.getString( "TLT_Vendor_Lot_Number" ) );
                transitLotNumber.setShipQty( rset.getInt ( "TLT_Ship_Qty" ) );
                waferId = rset.getString ( "Wafer_Id" );
                transitLotNumber.setWaferId(  ( waferId != null ) ? waferId.trim() : waferId );
                lotNumbers.add ( transitLotNumber );
            }
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could Not Load Transit Lot Numbers", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException ( "Could Not Commit", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException( "Could Not Rollback", e );
                        }
                    }
                }
            }

            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return lotNumbers;
    }

    /**
     * Returns the Part Number for the Given Lot Number and Process Type Name
     * @param lotNumber
     * @param processType
     * @throws Exception
     */
    public static String getPartNumberForLotNumberAndProcessType(  String lotNumber, String processType ) throws Exception
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset  = null;
        String partNumber = null;
        boolean success = false;

        try
        {
            conn    = DataSource.getConnection();
            pstmt   = conn.prepareStatement( SqlQueryMappings.get ( "operations.lot.getPartNumberForLotNumberAndProcessType" ) );
            pstmt.setString( 1, lotNumber );
            pstmt.setString( 2, processType );
            rset    = pstmt.executeQuery();

            if ( rset.next() )
            {
                partNumber = rset.getString("Part_Number");
            }
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Exception while getting the Part Number for the Given Lot Number and Process Type");
        }
        finally
        {
         if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return partNumber;
    }


    /**
     * This method is used to get the lot numbers which r in Assemly Release, Test, Test transit status
     * this mehtod will give the lot Numbers to be entered in Class store data Entry.
     * @param dbTransaction
     * @param processStepId
     * @return
     * @throws ScalarException
     */
    public static ArrayList getTestTransitLotNumbers ( DBTransaction dbTransaction, String processStepId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset  = null;
        ArrayList lotNumbers    = new ArrayList();
        TransitLotNumber transitLotNumber   = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            cstmt   = conn.prepareCall( SqlQueryMappings.get ( "operations.dataEntry.getTestTranisitLotNumbers" ) );
            cstmt.setString ( 1, processStepId );
            rset    = cstmt.executeQuery();

            while ( rset.next() )
            {
                transitLotNumber    = new TransitLotNumber();
                transitLotNumber.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                transitLotNumber.setNextLocation( rset.getString ( "VENDOR_LOCATION" ) );
                transitLotNumber.setShipQty( rset.getInt ( "SHIPPED_QTY" ) );
                lotNumbers.add ( transitLotNumber );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could Not Load TestTransit Lot Numbers", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return lotNumbers;
    }
     public static int getCSLNTQuantityShippedByLotId( DBTransaction dbTransaction, String lotId ) throws ScalarException
    {
        int qtyShipped = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.lot.CSLNTQuantityShipped.byLotId" ) );
            pstmt.setString ( 1, lotId );
            rset = pstmt.executeQuery();
            if ( rset.next () )
            {
                qtyShipped = rset.getInt ( "Qty_Shipped" );
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not fetch CSLNT Shipped Qty", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch( Exception ignore ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) { }
            }
        }
        return qtyShipped;
    }

    public static String getLotStatus(DBTransaction dbTransaction, String lotId) throws ScalarException
    {
		Connection conn = null;
		ResultSet rset    = null;
		PreparedStatement pstmt=null;
        String currentStatus = null;
		try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
			    conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT Lot_Current_Status FROM Lot_Table WHERE Lot_Id = ?" );
			pstmt.setString(1,lotId);
			rset=pstmt.executeQuery();
			if(rset.next())
            {
                currentStatus = rset.getString ( "Lot_Current_Status" );
			}
		}
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get lot status from database", sqle);
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch( SQLException ignore ){}
            }
	 		if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return currentStatus;
	}
}